Nested For XML from SQL Server table

Question

This may sound stupid (newer to SQL Server), but I am looking for help directing me on how to write a FOR XML query in SQL Serverto export a table to a very specific nested format. The program that I am importing the information into needs it this way or it will not work properly.

This is a sample of the table I am using.

Below is the output that I am looking for. I am able to generate the single row one with this query, but I am looking to nest it and make it look like the example below. I am not sure how to get the XML wrapper to be the value from the field. The end software that will be using this can only use ids that have numbers so that is why I have to format it the way that I am. Hoping someone can provide guidance and direction with this.

SELECT * 
FROM Tbl_Store
FOR XML Path('Store')

<STORE>
  <box1>
    <item1>
      <size>10</size>
      <weight>15</weight>
    </item1>
    <item2>
      <size>20</size>
      <weight>25</weight>
    </item2>
    <item3>
      <size>30</size>
      <weight>35</weight>
    </item3>
    <item4>
      <size>40</size>
      <weight>45</weight>
    </item4>
    <item5>
      <size>50</size>
      <weight>55</weight>
    </item5>
  </box1>
  <box2>
    <item1>
      <size>10</size>
      <weight>15</weight>
    </item1>
    <item2>
      <size>20</size>
      <weight>25</weight>
    </item2>
    <item3>
      <size>30</size>
      <weight>35</weight>
    </item3>
    <item4>
      <size>40</size>
      <weight>45</weight>
    </item4>
    <item5>
      <size>50</size>
      <weight>55</weight>
    </item5>
  </box2>
</Store>

Edit Finally version. Thanks to Shnugo

`SELECT ROW_NUMBER() OVER(ORDER BY Box) AS [@ID]
    ,M.Box as [BoxName]
        ,(
        SELECT m2.Item AS [@ID]
                ,m2.Size AS [size]
                ,m2.[Weight] AS [weight] 
        FROM @mockup m2
        WHERE m2.Box = m.Box
        ORDER BY m2.Item
        FOR XML PATH('item'),TYPE
        )
FROM @mockup m
GROUP BY m.Box 
FOR XML PATH('box'),ROOT('Store')`

Gottfried Lesigang · Accepted Answer · 2018-12-07 12:13:49Z

First of all: For the next question: Please do not post data as picture. It's no fun to type this in... Best was a [mcve], you can examine my answer how to provide consumable data in a table variable.

Secondly: It is a really bad idea to name-number the elements. One should never-ever do something like

<item1/>
<item2/>

And - to answer your question - this is not supported by FOR XML, at least not directly.

The problem is: Any query has dynamic elements (the data) and meta elements (the structure). You cannot alias a column dynamically (in other words: Use a data value as the name of a column in the result set).

If you cannot avoid to do it this way (I'd really recommend to re-check this option), we have to use a trick.

Create the whole statement dynamically and use EXEC()
Simulate the query and use string-concatenation:

Your data as mockup-table

DECLARE @mockup TABLE(Box VARCHAR(10),Item INT,Size INT,[Weight] INT);
INSERT INTO @mockup VALUES
 ('box1',1,10,15)
,('box1',2,20,25)
,('box1',3,30,35)
,('box1',4,40,45)
,('box1',5,50,55)
,('box2',1,10,15)
,('box2',2,20,25)
,('box2',3,30,35)
,('box2',4,40,45)
,('box2',5,50,55);

This works, but is super ugly and I'll have to wash my hands now :-)

SELECT CAST(
        CONCAT('<Store>'
             ,(
                SELECT CONCAT('<',Box,'>'
                      ,(
                        SELECT CONCAT('<item',Item,'>'
                              ,'<size>',m2.Size,'</size>'
                              ,'<weight>',m2.[Weight],'</weight>'
                              ,'</item',Item,'>')
                        FROM @mockup m2
                        WHERE m2.Box=m.Box
                        ORDER BY m2.Item
                        FOR XML PATH(''),TYPE).value('.','nvarchar(max)') 

                      ,'</',Box,'>')
                FROM @mockup m
                GROUP BY m.Box
                FOR XML PATH(''),TYPE).value('.','nvarchar(max)') 
             ,'</Store>')
        AS XML);

The result

<Store>
  <box1>
    <item1>
      <size>10</size>
      <weight>15</weight>
    </item1>
    <item2>
      <size>20</size>
      <weight>25</weight>
    </item2>
    <item3>
      <size>30</size>
      <weight>35</weight>
    </item3>
    <item4>
      <size>40</size>
      <weight>45</weight>
    </item4>
    <item5>
      <size>50</size>
      <weight>55</weight>
    </item5>
  </box1>
  <box2>
    <item1>
      <size>10</size>
      <weight>15</weight>
    </item1>
    <item2>
      <size>20</size>
      <weight>25</weight>
    </item2>
    <item3>
      <size>30</size>
      <weight>35</weight>
    </item3>
    <item4>
      <size>40</size>
      <weight>45</weight>
    </item4>
    <item5>
      <size>50</size>
      <weight>55</weight>
    </item5>
  </box2>
</Store>

This is, what you should do:

SELECT ROW_NUMBER() OVER(ORDER BY Box) AS [@index]
        ,(
        SELECT m2.Item AS [@index]
                ,m2.Size AS [size]
                ,m2.[Weight] AS [weight] 
        FROM @mockup m2
        WHERE m2.Box = m.Box
        ORDER BY m2.Item
        FOR XML PATH('item'),TYPE
        )
FROM @mockup m
GROUP BY m.Box 
FOR XML PATH('box'),ROOT('Store')

The result is close to your needed one, but properly designed:

<Store>
  <box index="1">
    <item index="1">
      <size>10</size>
      <weight>15</weight>
    </item>
    <item index="2">
      <size>20</size>
      <weight>25</weight>
    </item>
    <item index="3">
      <size>30</size>
      <weight>35</weight>
    </item>
    <item index="4">
      <size>40</size>
      <weight>45</weight>
    </item>
    <item index="5">
      <size>50</size>
      <weight>55</weight>
    </item>
  </box>
  <box index="2">
    <item index="1">
      <size>10</size>
      <weight>15</weight>
    </item>
    <item index="2">
      <size>20</size>
      <weight>25</weight>
    </item>
    <item index="3">
      <size>30</size>
      <weight>35</weight>
    </item>
    <item index="4">
      <size>40</size>
      <weight>45</weight>
    </item>
    <item index="5">
      <size>50</size>
      <weight>55</weight>
    </item>
  </box>
</Store>

This was my first post and I will take your suggestions for next time. Thank you for this. The messy version works, but I usual the properly designed one and was able to get it to work with the application that is importing it. I modified it slightly to include the box name for easier identification when reading the xml output. Also, instead of index I had to use ID for the application that I was importing it into. I edited my question with the finalized version at the bottom. Now just need to export to file as a stored procedure.

Collectives™ on Stack Overflow

Nested For XML from SQL Server table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related