How do I use one column for node names and the others for elements in that node?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » T-SQL (SS2K8) » How do I use one column for node names and...

Add to briefcase

30 posts, Page 1 of 3

How do I use one column for node names and the others for elements in that node?

Expand / Collapse

Author

Message

Timothy Graffham

Posted Tuesday, September 18, 2012 3:09 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 5:23 PM
Points: 21, Visits: 77

I'm trying to query some data with FOR XML to output the XML. Let's say I have data like this in my query result:

Part Color Size
123 blue small
124 black medium
125 red large

I want the xml to look like this:

<PartsList>
<123>
<Color>blue</Color>
<Size>small</Size>
</123>
<124>
<Color>black</Color>
<Size>medium</Size>
</124>
<125>
<Color>red</Color>
<Size>large</Size>
</125>
</PartsList>

I know this seems silly, but believe me the actual application that does this is pretty bad and it's way more complicated than this; I simplified. And yet, I have to figure out how to do this out of T-SQL and get this formatting.

Is this possible?

Thanks in advance,
Tim

Post #1361033

Posted Tuesday, September 18, 2012 6:32 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192

I think you're going to have a problem converting your part number into a tag.

You can do something like this:

DECLARE @t TABLE (Part INT, Color VARCHAR(10), Size VARCHAR(10))

INSERT INTO @t
SELECT 123,'blue','small'
UNION ALL SELECT 124,'black','medium'
UNION ALL SELECT 125,'red','large'

SELECT Part, Color, Size
FROM @t
FOR XML PATH(''), ROOT('PartsList')

Which gives you this:

<PartsList>
  <Part>123</Part>
  <Color>blue</Color>
  <Size>small</Size>
  <Part>124</Part>
  <Color>black</Color>
  <Size>medium</Size>
  <Part>125</Part>
  <Color>red</Color>
  <Size>large</Size>
</PartsList>

The above should be relatively easy to shred. Let me know if you need help with that (assuming it works for you).

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1361070

Posted Tuesday, September 18, 2012 8:42 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 PM
Points: 32,931, Visits: 26,820

As a side bar, this is one of the reasons for my strong dislike for XML.

The following XML takes 193 bytes including a single end-of-line character.

<PartsList>
<Part>123</Part>
<Color>blue</Color>
<Size>small</Size>
<Part>124</Part>
<Color>black</Color>
<Size>medium</Size>
<Part>125</Part>
<Color>red</Color>
<Size>large</Size>
</PartsList>

The following only takes 61 bytes and has all the same information in it.

Part,Color,Size
123,blue,small
124,black,medium
125,red,large

That's more than 3 times the number of bytes for XML. And some folks wonder why I/O is one of the biggest bottle-necks on some servers. Imagine what it would cost to get a 300% improvement by changing hardware. Wink

Wink

Considering that both the XML and the CSV are nothing more than flat data, is it really worth using XML for this? Even on hierarchical data, there's a terrible byte-count-bloat to pay when using XML not to mention what it takes to shred the stuff. It's much more effective to pass one CSV for each level of hierarchy... just like the tables where it all came from originally.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1361106

Posted Tuesday, September 18, 2012 8:55 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192

Jeff Moden (9/18/2012)

As a side bar, this is one of the reasons for my strong dislike for XML.

The following XML takes 193 bytes including a single end-of-line character.

<PartsList>
<Part>123</Part>
<Color>blue</Color>
<Size>small</Size>
<Part>124</Part>
<Color>black</Color>
<Size>medium</Size>
<Part>125</Part>
<Color>red</Color>
<Size>large</Size>
</PartsList>

The following only takes 61 bytes and has all the same information in it.

Part,Color,Size
123,blue,small
124,black,medium
125,red,large

That's more than 3 times the number of bytes for XML. And some folks wonder why I/O is one of the biggest bottle-necks on some servers. Imagine what it would cost to get a 300% improvement by changing hardware. Wink

Wink

Considering that both the XML and the CSV are nothing more than flat data, is it really worth using XML for this? Even on hierarchical data, there's a terrible byte-count-bloat to pay when using XML not to mention what it takes to shred the stuff. It's much more effective to pass one CSV for each level of hierarchy... just like the tables where it all came from originally.

I do have to agree with the byte-bloat issue.

Makes me wonder what the guys that proposed XML as the means for passing data between applications over the wire (as EDI for example were thinking). I understand they were more interested in standardizing the data transmissions than network traffic, but still.

Alas, it makes me pine for the days of flat files, ASCII characters and assembly language.

OMG! You've made me into CELKO! w00t

w00t

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1361110

Posted Tuesday, September 18, 2012 9:06 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 PM
Points: 32,931, Visits: 26,820

Consider ASCII characters 28 through 31 and you don't even have to worry about embedded elemental columns.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1361112

Posted Wednesday, September 19, 2012 1:23 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192

Jeff Moden (9/18/2012)

As a side bar, this is one of the reasons for my strong dislike for XML.

As a side bar, here is one of the reasons I shouldn't be allowed to write SQL that involves XML:
http://www.sqlservercentral.com/Forums/Topic1359470-1292-2.aspx#bm1360618

Tongue

Tongue

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1361166

Posted Wednesday, September 19, 2012 1:47 AM

SSC Eights!

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:30 AM
Points: 803, Visits: 2,124

The point of XML is to transfer complex data between systems in a uniform way, its not really meant for passing simple data and is overkill for the case given.

If we take the example of say an individual from the DVLA(DMV in the US) to a police central database, you have a variety of information that cannot be easily encapsulated in a single CSV row, so you would need several CSV files, one or more of which may get corrupted/lost in transmission.

However in XML you can encapuslate all that information in a single file, without having to worry about the duplicating data, or losing a single critical file.

The XML may be structured something like this

<Driver name="" LicenceNumber="">
<Licence detail>
<Convictions>
</Type>
</points>
</Issuedate>
</expireDate>
</ban duration>
</Fine>
</Conviction>
</Illnesses>
</Restrictions>
</Licence detail>
</Driver>
<Driver>
::::::::
</Driver>
<Driver>
::::::::
</Driver>

As you can appreciate this is simpler to decode at the receving end rather than multiple CSV's and I suspect that there wouldnt be that much more "bloat" overall, possibly the XML file will be smaller than the combined information in several CSV files.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1361174

Posted Wednesday, September 19, 2012 7:12 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:36 PM
Points: 32,931, Visits: 26,820

Jason-299789 (9/19/2012)

The point of XML is to transfer complex data between systems in a uniform way, its not really meant for passing simple data and is overkill for the case given.

If we take the example of say an individual from the DVLA(DMV in the US) to a police central database, you have a variety of information that cannot be easily encapsulated in a single CSV row, so you would need several CSV files, one or more of which may get corrupted/lost in transmission.

However in XML you can encapuslate all that information in a single file, without having to worry about the duplicating data, or losing a single critical file.

The XML may be structured something like this

<Driver name="" LicenceNumber="">
<Licence detail>
<Convictions>
</Type>
</points>
</Issuedate>
</expireDate>
</ban duration>
</Fine>
</Conviction>
</Illnesses>
</Restrictions>
</Licence detail>
</Driver>
<Driver>
::::::::
</Driver>
<Driver>
::::::::
</Driver>

As you can appreciate this is simpler to decode at the receving end rather than multiple CSV's and I suspect that there wouldnt be that much more "bloat" overall, possibly the XML file will be smaller than the combined information in several CSV files.

But you DO have a huge amount of duplicated data with XML in the form of tags. That's what the source of the bloat is.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1361310

Posted Wednesday, September 19, 2012 8:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

Jeff Moden (9/18/2012)

And some folks wonder why I/O is one of the biggest bottle-necks on some servers. Imagine what it would cost to get a 300% improvement by changing hardware. Wink

Wink

Considering that both the XML and the CSV are nothing more than flat data, is it really worth using XML for this? Even on hierarchical data, there's a terrible byte-count-bloat to pay when using XML not to mention what it takes to shred the stuff. It's much more effective to pass one CSV for each level of hierarchy... just like the tables where it all came from originally.

By this logic the architects of the internet were wrong and all web servers should be shipping flat files to our browsers for rendering Whistling

Whistling

This is another case-in-point of why it's a great idea to structure your environment in such a way that you can offload the parsing and processing of files, XML or flat, from the server where your database engine is hosted Wink

Wink

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1361378

Timothy Graffham

Posted Wednesday, September 19, 2012 10:32 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 5:23 PM
Points: 21, Visits: 77

I was very excited to go to my post this morning and see 8 responses. I thought I was going to get some real help. Instead, the first response was an attempt but didn't help really, and then everyone just used my post for a chat about general complaints with XML as a technology.

Very disappointing, folks. Can you please have those sort of conversations in their own threads?

My format that I illustrated in the original question was not flexible. I MUST display this information in this format even if I have to loop through the data procedurally and dynamically build it as a string. It's an installed base issue that I have to support.

My question is, is it possible? Can anyone tell me how to do this?

Post #1361496

« Prev Topic | Next Topic »

Add to briefcase

30 posts, Page 1 of 3

Permissions

Expand / Collapse