Creating XML output with Multiple sub nodes in SQL Server 2014

Question

I am trying to create an XML output using SQL. I am able to do it fine with basic lists, but not with list in lists. Basic Structure:

CREATE TABLE #TEMP1 (   ID1     INT
                        ,Names VARCHAR(10)
                        ,Address VARCHAR(50)
                )

Create Table #TEMP2 ( ID2 INT
                        ,ID1 INT
                        ,ITEM VARCHAR(10)
                        ,Quantity INT
                    )

INSERT INTO #TEMP1 (ID1,Names,Address)
VALUES (1,'Jack','Main St')
        ,(2,'Jill','Second St')
        ,(3,'Hill','3rd St')

INSERT INTO #TEMP2(ID2,ID1,ITEM,Quantity)
Values (1,1,'Curds',20)
        ,(2,2,'Way',30)
        ,(3,2,'Curds',40)
        ,(4,3,'Curds',50)
        ,(5,3,'Curds',60)
        ,(6,3,'Curds',70)

When I run the below XML, I get the desired result:

SELECT ID1      CusID
        ,Names  CusName
        ,Address    PrimAddress
FROM #TEMP1 Customer
FOR XML AUTO, ELEMENTS

<Customer>
    <CusID>1</CusID>
    <CusName>Jack</CusName>
    <PrimAddress>Main St</PrimAddress>
</Customer>
<Customer>
    <CusID>2</CusID>
    <CusName>Jill</CusName>
    <PrimAddress>Second St</PrimAddress>
</Customer>
<Customer>
    <CusID>3</CusID>
    <CusName>Hill</CusName>
    <PrimAddress>3rd St</PrimAddress>
</Customer>

The problem occurs when I try to add the orders (#TEMP2) into the mix.

SELECT ID1      CusID
        ,Names  CusName
        ,Address    PrimAddress
        ,(SELECT ID2 PRODUCTID
                ,ITEM   PRODUCTNAME
                ,Quantity
            FROM #TEMP2 Items
            where ID1 = Customer.ID1
            FOR XML AUTO, ELEMENTS)
FROM #TEMP1 Customer
FOR XML AUTO, ELEMENTS

OutPut has a bunch of special character symbols instead of clean XML:

 <Customer>
    <CusID>1</CusID>
    <CusName>Jack</CusName>
    <PrimAddress>Main St</PrimAddress>&lt;Items&gt;&lt;PRODUCTID&gt;1&lt;/PRODUCTID&gt;&lt;PRODUCTNAME&gt;Curds&lt;/PRODUCTNAME&gt;&lt;Quantity&gt;20&lt;/Quantity&gt;&lt;/Items&gt;</Customer>
<Customer>
    <CusID>2</CusID>
    <CusName>Jill</CusName>
    <PrimAddress>Second St</PrimAddress>&lt;Items&gt;&lt;PRODUCTID&gt;2&lt;/PRODUCTID&gt;&lt;PRODUCTNAME&gt;Way&lt;/PRODUCTNAME&gt;&lt;Quantity&gt;30&lt;/Quantity&gt;&lt;/Items&gt;&lt;Items&gt;&lt;PRODUCTID&gt;3&lt;/PRODUCTID&gt;&lt;PRODUCTNAME&gt;Curds&lt;/PRODUCTNAME&gt;&lt;Quantity&gt;40&lt;/Quantity&gt;&lt;/Items&gt;</Customer>
<Customer>
    <CusID>3</CusID>
    <CusName>Hill</CusName>
    <PrimAddress>3rd St</PrimAddress>&lt;Items&gt;&lt;PRODUCTID&gt;4&lt;/PRODUCTID&gt;&lt;PRODUCTNAME&gt;Curds&lt;/PRODUCTNAME&gt;&lt;Quantity&gt;50&lt;/Quantity&gt;&lt;/Items&gt;&lt;Items&gt;&lt;PRODUCTID&gt;5&lt;/PRODUCTID&gt;&lt;PRODUCTNAME&gt;Curds&lt;/PRODUCTNAME&gt;&lt;Quantity&gt;60&lt;/Quantity&gt;&lt;/Items&gt;&lt;Items&gt;&lt;PRODUCTID&gt;6&lt;/PRODUCTID&gt;&lt;PRODUCTNAME&gt;Curds&lt;/PRODUCTNAME&gt;&lt;Quantity&gt;70&lt;/Quantity&gt;&lt;/Items&gt;</Customer>

Desired outcome is something like the below. May not be perfect XML, but the main point is to remove all the special characters (not trying to do a replace or something like that) :

<Customer>
    <CusID>1</CusID>
    <CusName>Jack</CusName>
    <PrimAddress>Main St</PrimAddress>
        <Items>
            <PRODUCTID>1</PRODUCTID>
            <PRODUCTNAME>Curds</PRODUCTNAME>
            <Quantity>20</Quantity>
        </Items>
</Customer>
<Customer>
    <CusID>2</CusID>
    <CusName>Jill</CusName>
    <PrimAddress>Second St</PrimAddress>
    <Items>
        <Item></Item>
        <PRODUCTID>2</PRODUCTID>
        <PRODUCTNAME>Way</PRODUCTNAME>
        <Quantity>30</Quantity>
        </Item>
    </Items>
    <Items>
        <PRODUCTID>3</PRODUCTID>
        <PRODUCTNAME>Curds</PRODUCTNAME>
        <Quantity>40</Quantity>
    </Items>
</Customer>

Gottfried Lesigang · Accepted Answer · 2018-06-02 21:17:22Z

I think it's this you are looking for:

CREATE TABLE #TEMP1 (    ID1     INT
                        ,Names VARCHAR(10)
                        ,Address VARCHAR(50)
                )

Create Table #TEMP2 ( ID2 INT
                     ,ID1 INT
                     ,ITEM VARCHAR(10)
                     ,Quantity INT
                    )

INSERT INTO #TEMP1 (ID1,Names,Address)
VALUES (1,'Jack','Main St')
        ,(2,'Jill','Second St')
        ,(3,'Hill','3rd St')

INSERT INTO #TEMP2(ID2,ID1,ITEM,Quantity)
Values (1,1,'Curds',20)
        ,(2,2,'Way',30)
        ,(3,2,'Curds',40)
        ,(4,3,'Curds',50)
        ,(5,3,'Curds',60)
        ,(6,3,'Curds',70);

SELECT ID1 AS CusID
      ,Names AS CusName
      ,[Address] AS PrimAddress
      ,(SELECT items.ID2 AS PRODUCTID
              ,items.ITEM AS PRODUCTNAME
              ,items.Quantity
        FROM #TEMP2 Items
        WHERE items.ID1 = Customer.ID1
        FOR XML PATH('Item'),ROOT('Items'), TYPE) AS [*]
FROM #TEMP1 Customer
FOR XML PATH('Customer'),ROOT('Customers');

The result

<Customers>
  <Customer>
    <CusID>1</CusID>
    <CusName>Jack</CusName>
    <PrimAddress>Main St</PrimAddress>
    <Items>
      <Item>
        <PRODUCTID>1</PRODUCTID>
        <PRODUCTNAME>Curds</PRODUCTNAME>
        <Quantity>20</Quantity>
      </Item>
    </Items>
  </Customer>
  <Customer>
    <CusID>2</CusID>
    <CusName>Jill</CusName>
    <PrimAddress>Second St</PrimAddress>
    <Items>
      <Item>
        <PRODUCTID>2</PRODUCTID>
        <PRODUCTNAME>Way</PRODUCTNAME>
        <Quantity>30</Quantity>
      </Item>
      <Item>
        <PRODUCTID>3</PRODUCTID>
        <PRODUCTNAME>Curds</PRODUCTNAME>
        <Quantity>40</Quantity>
      </Item>
    </Items>
  </Customer>
  <Customer>
    <CusID>3</CusID>
    <CusName>Hill</CusName>
    <PrimAddress>3rd St</PrimAddress>
    <Items>
      <Item>
        <PRODUCTID>4</PRODUCTID>
        <PRODUCTNAME>Curds</PRODUCTNAME>
        <Quantity>50</Quantity>
      </Item>
      <Item>
        <PRODUCTID>5</PRODUCTID>
        <PRODUCTNAME>Curds</PRODUCTNAME>
        <Quantity>60</Quantity>
      </Item>
      <Item>
        <PRODUCTID>6</PRODUCTID>
        <PRODUCTNAME>Curds</PRODUCTNAME>
        <Quantity>70</Quantity>
      </Item>
    </Items>
  </Customer>
</Customers>

Holmes IV · Accepted Answer · 2018-05-30 18:24:49Z

0

Would have liked to see it add in the logic for looping throught the Items, but this works I think.

Select 
(SELECT ID1     CusID
        ,Names  CusName
        ,Address    PrimAddress
        ,(SELECT ID2 PRODUCTID
                ,ITEM   PRODUCTNAME
                ,Quantity
            FROM #TEMP2 Items
            where ID1 = Customer.ID1
            FOR XML PATH(''), TYPE, ELEMENTS) AS Items
FROM #TEMP1 Customer
FOR XML AUTO, ELEMENTS)

Thanks too : https://www.codeproject.com/Articles/54584/Controlling-the-XML-output-when-using-the-FOR-XML

answered May 30, 2018 at 18:24

Holmes IV

1,7694 gold badges26 silver badges54 bronze badges

Collectives™ on Stack Overflow

Creating XML output with Multiple sub nodes in SQL Server 2014

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related