Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How do I use one column for node names and the others for elements in that node? Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 3:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 5:31 PM
Points: 23, Visits: 91
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:30 PM
Points: 3,631, Visits: 5,281
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).



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1361070
Posted Tuesday, September 18, 2012 8:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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.

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1361106
Posted Tuesday, September 18, 2012 8:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:30 PM
Points: 3,631, Visits: 5,281
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.

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!



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1361110
Posted Tuesday, September 18, 2012 9:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1361112
Posted Wednesday, September 19, 2012 1:23 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:30 PM
Points: 3,631, Visits: 5,281
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




My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1361166
Posted Wednesday, September 19, 2012 1:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
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

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1361310
Posted Wednesday, September 19, 2012 8:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:34 AM
Points: 7,098, Visits: 12,606
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.

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

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



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1361378
Posted Wednesday, September 19, 2012 10:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 5:31 PM
Points: 23, Visits: 91
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 123»»»

Permissions Expand / Collapse