How to control the output format of XML in SQL

Question

I'm trying to retrieve data from a (SQL Server 2008 R2) table in XML format and can't seem to get it into an ideal structure. I've tried various FOR XML modes and join methods, but I have minimal experience with XML and have not worked with XML output via SQL prior to this.

My data is in a table named XMLResults with the following structure:

ResultID    Country Product Disposition Results
-----------------------------------------------
1           UK      ABC     Result1     100
2           UK      ABC     Result2     200
3           UK      ABC     Result3     300
4           UK      XYZ     Result1     100
5           UK      XYZ     Result2     200
6           UK      XYZ     Result3     300
7           USA     ABC     Result1     100
8           USA     ABC     Result2     200
9           USA     ABC     Result3     300
10          USA     XYZ     Result1     100
11          USA     XYZ     Result2     200
12          USA     XYZ     Result3     300

Currently I have this query:

SELECT (SELECT Country,
                (SELECT Product,
                        (SELECT Disposition, Results
                           FROM XMLResults t1
                          WHERE t1.ResultID = t2.ResultID
                            FOR XML PATH ('Dispositions'), TYPE, ELEMENTS
                        )
                   FROM XMLResults t2
                  WHERE t2.ResultID = t3.ResultID
                    FOR XML PATH ('Products'), TYPE, ELEMENTS
                )
          FROM XMLResults t3
         ORDER BY Country, Product
           FOR XML PATH ('Countries'), TYPE, ELEMENTS
        )
   FOR XML PATH('Stats');

Which returns the XML like this:

<Stats>
  <Countries>
    <Country>UK</Country>
    <Products>
      <Product>ABC</Product>
      <Dispositions>
        <Disposition>Result1</Disposition>
        <Results>100</Results>
      </Dispositions>
    </Products>
  </Countries>
  <Countries>
    <Country>UK</Country>
    <Products>
      <Product>ABC</Product>
      <Dispositions>
        <Disposition>Result2</Disposition>
        <Results>200</Results>
      </Dispositions>
    </Products>
  </Countries>
  <Countries>
    <Country>UK</Country>
    <Products>
      <Product>ABC</Product>
      <Dispositions>
        <Disposition>Result3</Disposition>
        <Results>300</Results>
      </Dispositions>
    </Products>
  </Countries>
  ...
</Stats>

Not terrible, but I'd prefer it not drop all the way back out to the 'Countries' level after each result, and probably don't need the extra generic tags either.

Something like this would be better:

<Stats>
  <Countries>
    <Country = "UK">
      <Products>
        <Product = "ABC">
          <Dispositions>
            <Disposition>
              <ReasonCode>Result1</ReasonCode>
              <Count>100</Count>
            </Disposition>
            <Disposition>
              <ReasonCode>Result2</ReasonCode>
              <Count>200</Count>
            </Disposition>
            <Disposition>
              <ReasonCode>Result3</ReasonCode>
              <Count>300</Count>
            </Disposition>
          </Dispositions>
        </Product>
        ...
      </Products>
    </Country>
    ...
  </Countries>
</Stats>

Maybe even something like this (looks a little bit cleaner):

<Stats>
  <Country = "UK">
    <Product = "ABC">
      <Disposition ReasonCode = "Result1" Count = "100" />
      <Disposition ReasonCode = "Result2" Count = "200" />
      <Disposition ReasonCode = "Result3" Count = "300" />
    </Product>
    <Product = "XYZ">
      <Disposition ReasonCode = "Result1" Count = "100" />
      <Disposition ReasonCode = "Result2" Count = "200" />
      <Disposition ReasonCode = "Result3" Count = "300" />
    </Product>
  </Country>
  <Country = "USA">
    <Product = "ABC">
      <Disposition ReasonCode = "Result1" Count = "100" />
      <Disposition ReasonCode = "Result2" Count = "200" />
      <Disposition ReasonCode = "Result3" Count = "300" />
    </Product>
    <Product = "XYZ">
      <Disposition ReasonCode = "Result1" Count = "100" />
      <Disposition ReasonCode = "Result2" Count = "200" />
      <Disposition ReasonCode = "Result3" Count = "300" />
    </Product>
  </Country>
</Stats>

The output format isn't set in stone, so if there are recommendations, I'm open to suggestions on that as well.

Sample data:

CREATE TABLE XMLResults (
      ResultID BIGINT IDENTITY(1,1) NOT NULL
    , Country VARCHAR(50) NOT NULL
    , Product VARCHAR(50) NOT NULL
    , Disposition VARCHAR(50) NOT NULL
    , Results INT NOT NULL);

INSERT INTO XMLResults (Country, Product, Disposition, Results)
VALUES ('UK',  'ABC', 'Result1', 100)
     , ('UK',  'ABC', 'Result2', 200)
     , ('UK',  'ABC', 'Result3', 300)
     , ('UK',  'XYZ', 'Result1', 100)
     , ('UK',  'XYZ', 'Result2', 200)
     , ('UK',  'XYZ', 'Result3', 300)
     , ('USA', 'ABC', 'Result1', 100)
     , ('USA', 'ABC', 'Result2', 200)
     , ('USA', 'ABC', 'Result3', 300)
     , ('USA', 'XYZ', 'Result1', 100)
     , ('USA', 'XYZ', 'Result2', 200)
     , ('USA', 'XYZ', 'Result3', 300);

It would be really helpful to have a SQL Fiddle set up that creates the tables and inserts some sample data into them .... — marc_s
– marc_s, Commented Dec 2, 2013 at 19:35

Anon · Accepted Answer · 2013-12-03 04:54:38Z

1

If you want attribute-centric xml, prepend '@' to your column aliases. You can completely customize the output, e.g:

SELECT
  Country [@Country],
  (SELECT
    Product [@Product],
    (SELECT
      Disposition [@ReasonCode],
      Results [@Count]
    FROM #XMLResults t3
    WHERE t3.Country = t1.Country AND t3.Product = t2.Product
    FOR XML PATH('Disposition'),TYPE)
  FROM #XMLResults  t2
  WHERE t2.Country = t1.Country
  GROUP BY Product
  FOR XML PATH('Product'),TYPE)
FROM #XMLResults  t1
GROUP BY Country
FOR XML PATH('Country'), ROOT ('Stats')

Just play around with it.

edited Dec 3, 2013 at 4:54

answered Dec 2, 2013 at 19:35

Anon

10.9k1 gold badge32 silver badges31 bronze badges

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

2 Comments

Sean Over a year ago

This is great except that it duplicates the Product section by the number of Dispositions for that product. In the sample data there's 3 results for each product, so each product, for each country, is displayed 3 times (rather than just once with the 3 results). Is there a way around this? I haven't been able to find a solution yet.

Anon Over a year ago

Answer changed to match requirements. I added GROUP BY Product for t2

Collectives™ on Stack Overflow

How to control the output format of XML in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related