How to insert element after root node in multiple xml output via FOR XML in SQL server

Question

I'm try use "FOR XML" feature in SQL Server. I am using SQL Server 2008 Express. I need XML results with SQL Server.

I have two tables, dbo.document and dbo.items.

Sample data:

First table [Document]:

     ID   info1  info2
-------   ----   ----- 
      1    11      22

Second table [Items]:

     ID   nvarchar1   nvarchar2   nvarchar3  docId
-------   ----        ----       ----        ----
      1    d          e          f            1
      2   dd          ee         ff           1

Based on Create single XML from SQL Server multiple tables query , Get nested XML output sql server and SQL Server “FOR XML” output from queries joining two tables my code is:

SELECT     TOP (1)Document.info1, Document.info2,
(SELECT     TOP (3)
nvarchar1,
nvarchar2,
nvarchar3
FROM         Items 
WHERE Items.docId = Document.id
FOR XML PATH ('myitem'), TYPE)
FROM         Document
order by id desc
FOR XML PATH ('List'), ROOT ('myroot')

And result is:

 <myroot>    
      <List>
      <info1>11</info1>
      <info2>22</info2> 
        <myitem>
          <nvarchar1>d</nvarchar1>
          <nvarchar2>e</nvarchar2>
          <nvarchar3>f</nvarchar3>
        </myitem>
        <myitem>
          <nvarchar1>dd</nvarchar1>
          <nvarchar2>ee</nvarchar2>
          <nvarchar3>ff</nvarchar3>
        </myitem>
      </List>
    </myroot>

But I want xml in following format: (insert elements info1, info2 directly after myroot)

<myroot>    
<info1>11</info1>
<info2>22</info2>   
      <List>
        <myitem>
          <nvarchar1>d</nvarchar1>
          <nvarchar2>e</nvarchar2>
          <nvarchar3>f</nvarchar3>
        </myitem>
        <myitem>
          <nvarchar1>dd</nvarchar1>
          <nvarchar2>ee</nvarchar2>
          <nvarchar3>ff</nvarchar3>
        </myitem>
      </List>
    </myroot>

Does anyone have any solutions for this case? Thanks a lot.
Note: Next variant: info1,info 2 will be stored in third table.

Ric Guerrero · Accepted Answer · 2017-02-05 10:48:16Z

0

Try setting the ROOT('List') to the nested select statement, then set the PATH of the outer select to '' like this:

SELECT TOP (1)
        Document.info1
        , Document.info2
        ,(
            SELECT TOP (3)
                    nvarchar1
                    ,nvarchar2
                    ,nvarchar3
                FROM Items
                WHERE
                    Items.docId = Document.id
                FOR XML PATH ('myitem'),ROOT ('List'), TYPE
        )
    FROM Document
    ORDER BY ID DESC
    FOR XML PATH (''), ROOT ('myroot')

That should work.

answered Feb 5, 2017 at 10:48

Ric Guerrero

664 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pali Over a year ago

Yes, thank you @RicGuerrero. I have one more question: what If I need put <myitem><nvarchar1></nvarchar1>...<myitem/> between next leve like <myitem_list><myitem><nvarchar1></nvarchar1>...</myitem></myitem_list> ----> so the final result will be <myroot><List><myitem_list><myitem><nvarchar1></nvarchar1>...</myitem></myitem_list></List></myroot> ?

Collectives™ on Stack Overflow

How to insert element after root node in multiple xml output via FOR XML in SQL server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related