CODEWITHSUNDEEP
sqlxmlsql-server-2008t-sqlnested
J. Kruijt
J. Kruijt

Reputation: 66

Need multiple tags in FOR XML result

I try to output XML text where the output rows have more then one root tag and the selection result also has more then one root tag. I understand this is a bit vague so let me try to illustrate the problem. The real environment is rather complex so I have tried to simplify the srtucture.

In case of this simplified example there are 2 tables involved. One named xml_orderlines and one xml_clients. Table structure is as follows:

For 1 client there are 1 or more orderlines. I know we should have also a table Orders within this structure but for the example it is not applicable)

To create xml_orderlines:

CREATE TABLE [dbo].[xml_OrderLines](
[orderid] [int] NULL,
[cl_id] [int] NULL,
[DtOrder] [date] NULL,
[PlcOfOrder] [varchar](50) NULL,
[Accman] [varchar](50) NULL,
[Art_id] [int] NULL
) ON [PRIMARY]

GO

INSERT INTO xml_OrderLines (orderid,cl_id,DtOrder,PlcOfOrder,Accman,art_id) VALUES (12,101,'2017-05-31','Rotterdam',201,301)
INSERT INTO xml_OrderLines (orderid,cl_id,DtOrder,PlcOfOrder,Accman,art_id) VALUES (13,102,'2017-06-15','Amsterdam',202,302)
INSERT INTO xml_OrderLines (orderid,cl_id,DtOrder,PlcOfOrder,Accman,art_id) VALUES (14,103,'2017-07-15','London',203,303)
INSERT INTO xml_OrderLines (orderid,cl_id,DtOrder,PlcOfOrder,Accman,art_id) VALUES (15,104,'2017-08-15','Dublin',204,304)
INSERT INTO xml_OrderLines (orderid,cl_id,DtOrder,PlcOfOrder,Accman,art_id) VALUES (16,101,'2017-05-31','Rotterdam',205,305)
INSERT INTO xml_OrderLines (orderid,cl_id,DtOrder,PlcOfOrder,Accman,art_id) VALUES (17,102,'2017-06-15','Amsterdam',206,306)
INSERT INTO xml_OrderLines (orderid,cl_id,DtOrder,PlcOfOrder,Accman,art_id) VALUES (18,103,'2017-07-15','London',207,307)
INSERT INTO xml_OrderLines (orderid,cl_id,DtOrder,PlcOfOrder,Accman,art_id) VALUES (19,104,'2017-08-15','Dublin',208,308)
INSERT INTO xml_OrderLines (orderid,cl_id,DtOrder,PlcOfOrder,Accman,art_id) VALUES (18,103,'2017-07-15','London',207,307)

To create xml_cients:

CREATE TABLE [dbo].[xml_clients](
[cl_id] [int] NULL,
[name] [varchar](50) NULL,
[surname] [varchar](50) NULL,
[DtOfBirth] [date] NULL
) ON [PRIMARY]

GO

INSERT INTO xml_Clients  (cl_id,name,surname,DtOfBirth) VALUES (101,'Jan','Winter','2000-05-31')
INSERT INTO xml_Clients  (cl_id,name,surname,DtOfBirth) VALUES (102,'John','Summer','2000-06-15')
INSERT INTO xml_Clients  (cl_id,name,surname,DtOfBirth) VALUES (103,'Jean','Autum','2000-05-12')
INSERT INTO xml_Clients  (cl_id,name,surname,DtOfBirth) VALUES (104,'Janis','Spring','2000-06-16')

This is the query I run:

SELECT 
ol.Art_id   AS 'ArtikelOrder'
,
    (
    SELECT
        c.cl_id AS 'Buyer/Client'
    ,   c.name  AS 'Buyer/FirstName'
    ,   c.surname   AS 'Buyer/SurName'
    ,   c.DtOfBirth AS 'Buyer/DateOfBirth'
    from 
        xml_clients AS c
    join
        xml_orderlines AS x ON c.cl_id=x.cl_id

    WHERE x.art_id = ol.art_id
    FOR XML PATH(''), TYPE, ROOT('Clients')
    )
,   ol.DtOrder  AS 'OrderDate'
,   ol.plcOfOrder AS 'OrderPlace'
,   ol.orderid AS 'OrderId'
FROM
xml_orderlines AS ol 
FOR XML PATH('Tx'),TYPE, ROOT('Document')

This query gives me the following result in XML:

<Document>
 <Tx>
  <ArtikelOrder>307</ArtikelOrder>
  <Clients>
    <Buyer>
      <Client>103</Client>
      <FirstName>Jean</FirstName>
      <SurName>Autum</SurName>
      <DateOfBirth>2000-05-12</DateOfBirth>
    </Buyer>
    <Buyer>
      <Client>103</Client>
      <FirstName>Jean</FirstName>
      <SurName>Autum</SurName>
      <DateOfBirth>2000-05-12</DateOfBirth>
    </Buyer>
  </Clients>
  <OrderDate>2017-07-15</OrderDate>
  <OrderPlace>London</OrderPlace>
  <OrderId>18</OrderId>
 </Tx>
 <Tx>
   <ArtikelOrder>302</ArtikelOrder>
    <Clients>
    <Buyer>
      <Client>102</Client>
      <FirstName>John</FirstName>
      <SurName>Summer</SurName>
      <DateOfBirth>2000-06-15</DateOfBirth>
    </Buyer>
   </Clients>
 <OrderDate>2017-06-15</OrderDate>
 <OrderPlace>Amsterdam</OrderPlace>
 <OrderId>13</OrderId>
</Tx>
</Document>

I try to achieve the following output:

<FirstQRoot>
 <Document>
  <FirstRowRoot>
   <Tx>
    <ArtikelOrder>307</ArtikelOrder>
    <Clients>
     <Buyer>
      <Client>103</Client>
      <FirstName>Jean</FirstName>
      <SurName>Autum</SurName>
      <DateOfBirth>2000-05-12</DateOfBirth>
     </Buyer>
     <Buyer>
      <Client>103</Client>
      <FirstName>Jean</FirstName>
      <SurName>Autum</SurName>
      <DateOfBirth>2000-05-12</DateOfBirth>
     </Buyer>
    </Clients>
    <OrderDate>2017-07-15</OrderDate>
    <OrderPlace>London</OrderPlace>
    <OrderId>18</OrderId>
   </Tx>
  </FirstRowRoot>
  <FirstRowRoot>
   <Tx>
   <ArtikelOrder>302</ArtikelOrder>
    <Clients>
     <Buyer>
      <Client>102</Client>
      <FirstName>John</FirstName>
      <SurName>Summer</SurName>
      <DateOfBirth>2000-06-15</DateOfBirth>
     </Buyer>
    </Clients>
    <OrderDate>2017-06-15</OrderDate>
    <OrderPlace>Amsterdam</OrderPlace>
    <OrderId>13</OrderId>
   </Tx>
  </FirstRowRoot>
 </Document>
</FirstQRoot>

I hope that it is possible to add the "FirstQRoot" and "FirstRowRoot" tags. I have tried several combinations of sub queries and dual tags (with the / in the tag name) but none of them solved the issue.

Upvotes: 0

Views: 1465

Answers (2)

Serg
Serg

Reputation: 22811

Demo Initial query:

select x,y
from (
    --demo data
     select 1 as x, 10 as y
     union all
     select 2, 20
) t 
for xml path('Tx'), type, root ('Document');

returns

<Document>
  <Tx>
    <x>1</x>
    <y>10</y>
  </Tx>
  <Tx>
    <x>2</x>
    <y>20</y>
  </Tx>
</Document>

Refactored query:

select Document
from (
    select x as [Tx/x],y as [Tx/y] 
      from (
         --demo data
         select 1 as x, 10 as y
         union all
         select 2, 20) t 
    for xml path('FirstRowRoot'), type
) t2(Document)
for xml path(''), type, root('FirstQRoot');

Using the updated question's data:

SELECT Document
FROM(
    SELECT 
    ol.Art_id   AS [Tx/ArtikelOrder]
    ,(
        SELECT
            c.cl_id AS 'Buyer/Client'
        ,   c.name  AS 'Buyer/FirstName'
        ,   c.surname   AS 'Buyer/SurName'
        ,   c.DtOfBirth AS 'Buyer/DateOfBirth'
        from 
            xml_clients AS c
        join
            xml_orderlines AS x ON c.cl_id=x.cl_id

        WHERE x.art_id = ol.art_id
        FOR XML PATH(''), TYPE
     ) AS [Tx/Clients]
    ,   ol.DtOrder  AS [Tx/OrderDate]
    ,   ol.plcOfOrder AS [Tx/OrderPlace]
    ,   ol.orderid AS [Tx/OrderId]
    FROM
    xml_orderlines AS ol 
    FOR XML PATH('FirstRowRoot'),TYPE
) t(Document)
FOR XML PATH(''), TYPE, ROOT('FirstQRoot');

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

It is a bit tricky to add an internal two nested row-root... Try it like this:

SELECT 
(
    SELECT
    (
        SELECT ol.Art_id AS ArtikelOrder
              ,(
                SELECT
                    c.cl_id AS 'Client'
                ,   c.name  AS 'FirstName'
                ,   c.surname   AS 'SurName'
                ,   c.DtOfBirth AS 'DateOfBirth'
                from xml_clients AS c
                join xml_orderlines AS x ON c.cl_id=x.cl_id
                WHERE x.art_id = ol.art_id
                FOR XML PATH('Buyer'), ROOT('Clients'),TYPE
               )
              ,ol.DtOrder AS OrderDate
              ,ol.PlcOfOrder AS OrderPlace
              ,ol.orderid AS OrderId
        FOR XML PATH('Tx'),ROOT('FirstRowRoot'),TYPE
    )
    FROM xml_OrderLines AS ol
    FOR XML PATH(''),ROOT('Document'),TYPE
)
FOR XML PATH('FirstQRoot');

Upvotes: 1

Related Questions