Convert tabular data to XML using SQL Server

Question

I have a flattened table that contains columns that represent groups that need to be displayed in XML. Example data:

Market, Label, Style, Type
XXX,    YYY,   JJJ,   111
XXX,    YYY,   JJJ,   222
XXX,    YYY,   JJJ,   333
XXX,    YYY,   JJJ,   444    
XXX,    YYY,   LLL,   111    
XXX,    YYY,   LLL,   222    
XXX,    YYY,   LLL,   333    
XXX,    YYY,   LLL,   444

Using T-SQL what would be the best way to output the following:

<Market value=XXX>
    <label value=YYY>
       <Style value=JJJ>
          <Type value=111>
          </Type>
          ...
       </Style>
       <Style value=LLL>
          ...
       </Style>
    </label>
</Market>

Can I do this by using the XML Explicit clause in SQL Server?

Thakur · Accepted Answer · 2010-09-28 07:58:02Z

2

This might help.. take a look

SELECT distinct MyTable.Market "Market/@Value",
        MyTable.Label "Market/Label/@Value",
        MyTable.Style "Market/Label/Style/@Value",

             (SELECT Type AS  "Value"
               FROM   MyTable myTab
               WHERE myTab.Market=MyTable.Market
                        and myTab.Label=MyTable.Label
                        and myTab.Style = MyTable.Style
               FOR XML PATH ('')
               ) AS "Market/Label/Style/Type"
        FROM MyTable  
FOR XML PATH('')

Resultant XML was :

<Market Value="XXX">
  <Label Value="YYY">
    <Style Value="JJJ">
      <Type>&lt;Value&gt;111&lt;/Value&gt;&lt;Value&gt;222&lt;/Value&gt;&lt;Value&gt;333&lt;/Value&gt;&lt;Value&gt;444&lt;/Value&gt;</Type>
    </Style>
  </Label>
</Market>
<Market Value="XXX">
  <Label Value="YYY">
    <Style Value="LLL">
      <Type>&lt;Value&gt;111&lt;/Value&gt;&lt;Value&gt;123&lt;/Value&gt;</Type>
    </Style>
  </Label>
</Market>

Hope it helps

answered Sep 28, 2010 at 7:58

Thakur

2,0306 gold badges24 silver badges34 bronze badges

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

1 Comment

Panaymel Over a year ago

Thanks for that. Its so close but when I add more data it does not correctly group by. I think I will the answer that was left by Robert it might be easier to do it in code.

Robert Rossney · Accepted Answer · 2010-09-28 09:04:04Z

1

Formatting complex XML documents in T-SQL is a fool's errand. It can be done - maybe - but then you come back to it a month later and what you've got is incomprehensible.

It's much, much easier to either write a method in C# or whatever that processes a DataReader to produce the XML, or write an XSLT transform that converts the XML emitted from the query into whatever specialized format you're trying to create.

answered Sep 28, 2010 at 9:04

Robert Rossney

97.3k24 gold badges150 silver badges218 bronze badges

1 Comment

OMG Ponies Over a year ago

The only reason it's easier to do in C#/etc is practice & familiarity. XML generation via [T]SQL won't get any easier if you never work with it.

fritz · Accepted Answer · 2010-09-28 04:32:16Z

0

there are 3 shaping method to generate table to xml in SQL Server 2000:

for xml auto -> generate the query to xml automatically based on using table
for xml raw -> generate the query table to row tag in xml
for xml explicit -> the most complex but most comfort way :) you can defined your own xml structure

for the cheat sheet you can see this link

hope this can help you.

answered Sep 28, 2010 at 4:32

fritz

1514 bronze badges

3 Comments

OMG Ponies Over a year ago

There's four, PATH is 2005+

Panaymel Over a year ago

Thanks for the link. I have had a look at the 3 options. All of them seem to offer examples that use multiple tables my problem uses only one table to create the XML hierarchy. Not sure if it can be done in SQL?

fritz Over a year ago

i'm pretty sure it will work. cause the tuts is for sql server :)

Aaron Hudon · Accepted Answer · 2012-10-04 18:27:40Z

0

Have a look at the SQLXML library. You can create an annotated XSD file that will produce an XML document.

http://msdn.microsoft.com/en-us/library/ms172699

Just be forewarned that the performance of this method is quite bad. I have made multiple requests to Microsoft to look at this issue. I have compared hand-writing a "FOR XML" query that takes milliseconds to finish, and the annotated XSD version takes 10 seconds.

I personally think that annotated XSDs are a very elegant solution if you can work around the poor performance.

answered Oct 4, 2012 at 18:27

Aaron Hudon

5,9794 gold badges61 silver badges63 bronze badges

Collectives™ on Stack Overflow

Convert tabular data to XML using SQL Server

4 Answers 4

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related