Is there a way to put a header and footer in XML output when using SQL-CodePudding

I’m writing a query that returns data in XML format and I need to add a header that contains some static data, and a footer that contains a count of all of the records in the file. is there a way to do that so that the header and footer only appear once in the file?

my example query is as follows:

SET QUOTED_IDENTIFIER on
SET NOCOUNT ON
DECLARE @XMLTEST XML

SELECT @XMLTEST = (

SELECT GOLFER_NAME AS [GENERAL/NAME],
       GOLFER_ID AS [GENERAL/ID],
       HANDICAP_INDEX AS [ATTRIBUTES/HANDICAP],
       GOLFER_AGE AS [ATTRIBUTES/AGE]
FROM GOLFERS

FOR XML PATH ('row'), Root('root')
)

SET @XMLTEST.modify(
       'insert
        (
         attribute xsi:noNamespaceSchemaLocation {"ARIS_Inbound-v4.0.xsd"}
)
into (/GolfData)[1]')

SELECT @XMLTEST

This returns data in the following format:

<root>
 <row>
  <general>
   <name>woods,tiger</name>
   <id>1</id>
  </general>
  <attributes>
   <handicap>4</handicap>
   <age>45</age>
   </attributes>
  </row>
  <row>
   <general>
    <name>fowler,ricky</name>
    <id>2</id>
   </general>
   <attributes>
    <handicap>7</handicap>
    <age>39</age>
   </attributes>
  </row>
</root>

Which is what i am looking for but I need to have a header in the file with a format of:

<header>
<version>1.2.113</version>
<author>lincoln,abraham</author>
</header>

and a footer that counts the number of entries (or rows in this case) like this:

<trailer>
 <rowcount>2</rowcount>
</trailer>

is this possible?

CodePudding user response：

perhaps I'm overlooking the obvious, but couldn't you concatenate your normal results with your header and footer? Something like:

declare @header varchar(max) = "your header" 
declare @footerstart varchar(max) = "your footer start"
declare @footercount int;
declare @footerend varchar(max) = "your footer end"
declare @xmlMiddle varchar(max)

select @xmlMiddle = -- your code that generates the full XML.

--add a select count(*) from your dataset like
select @footercount = from.... where...

select @header   @xmlMiddle   @footerstart   @footercount   @footerend

CodePudding user response：

thank you. the last bit helped me get over the finish line and get it as i wanted. thanks!