How to use XML AUTO with SQL to create a structure with 2 nested tables

Question

thank you for reading. I'm currently trying to use XML AUTO with 3 tables to produce an xml document with 1 top level and 2 different tables at a sub level.

A simpler example with 2 tables:

SELECT
  dbo.SalesOrder.SupplementaryReference AS SupplementaryReference,
  dbo.SalesOrder.Address AS Address,
  dbo.SalesOrder.Address2 AS Address2,
  dbo.SalesOrder.CardCode AS CardCode,
  dbo.SalesOrder.DocDate AS DocDate,
  dbo.SalesOrder.DocDueDate AS DocDueDate,
  dbo.SalesOrderLines.ItemCode AS ItemCode,
  dbo.SalesOrderLines.Quantity AS Quantity
FROM
  dbo.SalesOrderLines,
  dbo.SalesOrder
WHERE
  dbo.SalesOrderLines.SupplementaryReference = dbo.SalesOrder.SupplementaryReference
FOR XML AUTO, ELEMENTS, ROOT('root')

This produces an output xml document like this:

<root>
  <dbo.SalesOrder>
    <Address>...</Address>
    <Address2>...</Address2>
    .....
    .....
    <dbo.SalesOrderLines>
      <ItemCode>...</ItemCode>
      <Quantity>...</Quantity>
    </dbo.SalesOrderLines>
  </dbo.SalesOrder>
</root>

This is what I would expect, however I'm attempting to get an XML document as describe below from 3 tables SalesOrder, SalesOrderLines, and SalesOrderExpenses:

<root>
  <dbo.SalesOrder>
    <Address>...</Address>
    <Address2>...</Address2>
    .....
    .....
    <dbo.SalesOrderLines>
      <ItemCode>...</ItemCode>
      <Quantity>...</Quantity>
    </dbo.SalesOrderLines>
    <dbo.SalesOrderExpenses>
      <BaseType>...</BaseType>
      <ExpnsCode>...</ExpnsCode>
      <LineTotal>...</LineTotal>
    </dbo.SalesOrderExpenses>
  </dbo.SalesOrder>
</root>

I have attempted doing the following query:

SELECT
  dbo.SalesOrder.SupplementaryReference AS SupplementaryReference,
  dbo.SalesOrder.Address AS Address,
  dbo.SalesOrder.Address2 AS Address2,
  dbo.SalesOrder.CardCode AS CardCode,
  dbo.SalesOrder.DocDate AS DocDate,
  dbo.SalesOrder.DocDueDate AS DocDueDate,
  dbo.SelesOrderExpenses.BasteType AS BaseType,
  dbo.SelesOrderExpenses.ExpnsCode AS ExpnsCode,
  dbo.SelesOrderExpenses.LineTotal AS LineTotal,
  dbo.SalesOrderLines.ItemCode AS ItemCode,
  dbo.SalesOrderLines.Quantity AS Quantity
FROM
  dbo.SalesOrderLines,
  dbo.SalesOrderExpenses,
  dbo.SalesOrder
WHERE
  dbo.SalesOrderLines.SupplementaryReference = dbo.SalesOrder.SupplementaryReference
FOR XML AUTO, ELEMENTS, ROOT('root')

however I receive this output which nests the third table inside the second:

<root>
  <dbo.SalesOrder>
    <Address>...</Address>
    <Address2>...</Address2>
    .....
    .....
    <dbo.SalesOrderExpenses>
      <BaseType>...</BaseType>
      <ExpnsCode>...</ExpnsCode>
      <LineTotal>...</LineTotal>
      <dbo.SalesOrderLines>
        <ItemCode>...</ItemCode>
        <Quantity>...</Quantity>
      </dbo.SalesOrderLines>
    </dbo.SalesOrderExpenses>
  </dbo.SalesOrder>
</root>

So any ideas? Thank you for your time.

TT. · Accepted Answer · 2016-11-16 15:50:53Z

1

Without sample data it's a bit hard to give advice, but you might be looking for something like this:

SELECT so.[Address]
      ,so.Address2
      /*more columns*/
      ,(
        SELECT sol.ItemCode
              ,sol.Quantity 
        FROM dbo.SalesOrderLines AS sol
        WHERE so.SupplementaryReference =sol.SupplementaryReference 
        FOR XML PATH('dbo.SalesOrderLines'),TYPE --only one row???
       ) AS [node()]
      ,(
        SELECT sol.ItemCode
              ,sol.Quantity 
        FROM dbo.SalesOrderExpenses AS soe
        WHERE so.SupplementaryReference =soe.SupplementaryReference 
        FOR XML PATH('dbo.SalesOrderLines'),TYPE --only one row???
       ) AS [node()]
FROM dbo.SalesOrder AS so

Btw: Avoid old fashioned joins where you add table names with comma and set the joining predicate in the WHERE clause. Better use the appropriate JOIN. In this case this an INNER JOIN...

edited Nov 16, 2016 at 15:50

TT.

16.2k6 gold badges53 silver badges95 bronze badges

answered Nov 16, 2016 at 15:41

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

Collectives™ on Stack Overflow

How to use XML AUTO with SQL to create a structure with 2 nested tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related