Search code examples
sql-serverxmlxml-namespacesfor-xml-path

Root element with header and detail rows

I use a for xml select to produce an xml file. I want to have two root nodes that contain some header tags and then detail rows that are a result from a query on a table.

Example: 

<Root>
 <FileHeader>

  HEADER ROWS

 </FileHeader>
 <Jobs>
  <Message xmlns="url">
   <Header Destination="1" xmlns="url"/>
   <Body>
     <ListItem xmlns="url">

       DETAIL ROWS FROM SELECT 

     </ListItem>

   </Body>
  </Message>
 </Jobs>
</Root>

The query I am trying to produce this is this one:

WITH XMLNAMESPACES('url')
SELECT(
SELECT   

 HEADER ROWS

FOR XML PATH('FileHeader'),
TYPE),
(SELECT  

         '1'     AS 'Message/Header/@Destination',
         'url'   AS 'Message/Header/@xmlns'

FOR XML PATH(''),
TYPE), 
(SELECT  

 DETAIL ROWS FROM SELECT 

FROM MY_TABLE
FOR XML PATH('Jobs'),ROOT('Body'),   
TYPE      )
FOR XML PATH ('Root')

MY_table and its data are irrelevant as all tags inside the final select are correct are validated against the xsd schema. The FileHeader and Header tags are populated with values given from variables, so no tables are used there.

I am missing something on the middle part of the query (the second select). With my way, I can't have the Header tag inside the Jobs/Body path.

What is more, I cannot fill in the with xmlns value. I even used the following as I found on some forums and still can't manage to produce a well formatted tag with the xmlns attribute.

;WITH XMLNAMESPACES ('url' as xmlns)

Thank you!

Solution

  • Some things to state first:

    • It is not allowed to add the namespaces xmlns like any other attribute
    • It is possible to add this default namespace with WITH XMLNAMESPACES. But - in cases of sub-queries - this namespace will be inserte repeatedly. This is not wrong yet annoying and it can blow up your XML and make it fairly hard to read...
    • You can create a nested XML ad-hoc (inlined), or prepare it first and insert it to the final query. This allows you to add default namespace to a deeper level only.

    It's not absolutely clear to me, what you are really looking for, but this might point you in the right direction:

    DECLARE @HeaderData TABLE(SomeValue INT,SomeText VARCHAR(100));
DECLARE @DetailData TABLE(DetailsID INT,DetailText VARCHAR(100));

INSERT INTO @HeaderData VALUES
 (100,'Value 100')
,(200,'Value 200');

INSERT INTO @DetailData VALUES
 (1,'Detail 1')
,(2,'Detail 2');

DECLARE @BodyWithNamespace XML;
WITH XMLNAMESPACES(DEFAULT 'SomeURL_for_Body_default_ns')
SELECT @BodyWithNamespace=
(
    SELECT *
    FROM @DetailData AS dd
    FOR XML PATH('DetailRow'),ROOT('ListItem'),TYPE
);

SELECT(
        SELECT *    
        FROM  @HeaderData AS hd
        FOR XML PATH('HeaderRow'),ROOT('FileHeader'),TYPE
      )
      ,
      (
        SELECT 1 AS [Header/@Destination]
              ,@BodyWithNamespace
        FOR XML PATH('Message'),ROOT('Jobs'),TYPE
      )
FOR XML PATH ('Root')

    The result

    <Root>
  <FileHeader>
    <HeaderRow>
      <SomeValue>100</SomeValue>
      <SomeText>Value 100</SomeText>
    </HeaderRow>
    <HeaderRow>
      <SomeValue>200</SomeValue>
      <SomeText>Value 200</SomeText>
    </HeaderRow>
  </FileHeader>
  <Jobs>
    <Message>
      <Header Destination="1" />
      <ListItem xmlns="SomeURL_for_Body_default_ns">
        <DetailRow>
          <DetailsID>1</DetailsID>
          <DetailText>Detail 1</DetailText>
        </DetailRow>
        <DetailRow>
          <DetailsID>2</DetailsID>
          <DetailText>Detail 2</DetailText>
        </DetailRow>
      </ListItem>
    </Message>
  </Jobs>
</Root>