September 18, 2012 at 3:09 pm
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
September 18, 2012 at 6:32 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 18, 2012 at 8:42 pm
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
Change is inevitable... Change for the better is not.
September 18, 2012 at 8:55 pm
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,Size123,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! :w00t:
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 18, 2012 at 9:06 pm
Consider ASCII characters 28 through 31 and you don't even have to worry about embedded elemental columns.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 19, 2012 at 1:23 am
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 19, 2012 at 1:47 am
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
September 19, 2012 at 7:12 am
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
Change is inevitable... Change for the better is not.
September 19, 2012 at 8:06 am
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 :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 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 19, 2012 at 10:32 am
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?
September 19, 2012 at 11:39 am
Timothy Graffham (9/19/2012)
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?
All fair points. These forums are in a 'discussion format' as opposed to a 'Q&A format' like http://ask.sqlservercentral.com and sometimes the ensuing discussions tend to dwarf the fact that sometimes people are just looking for a straight-forward answer to their question 🙂
The answer to your question is not simple. You have big challenges because the app is expecting data values to be used as element names which is outside what FOR XML provides. You might want to think about building the XML 'by hand', i.e. by using a variable to build a string in the form the app wants, but that will be a hellish coding assignment.
....
I found this link that asks a similar question: http://stackoverflow.com/questions/3240297/how-do-i-use-column-values-as-xml-element-names-using-for-xml-in-sql-server-2005
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 19, 2012 at 12:04 pm
...
I want the xml to look like this:
<PartsList>
<123>
...
It is not valid XML. You cannot have element named as number.
One of XML Naming Rule is: Names cannot start with a number or punctuation character
http://www.w3schools.com/xml/xml_elements.asp
But if you still want it as non-XML text, you can do the following with Dwain solution:
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 * from @t
DECLARE @xml VARCHAR(max)
SELECT @xml = (
SELECT Part, Color, Size, '/' + cast(Part as varchar) AS PartC
FROM @t
FOR XML PATH(''), ROOT('PartsList') )
SELECT REPLACE(REPLACE(REPLACE(REPLACE( @xml,'</Part>','>'),'<Part>','<'),'</PartC>','>'),'<PartC>','<')
Please Note, you will not be able to load above text to DOM object for use as XML...
September 19, 2012 at 1:32 pm
Assuming you fix your naming convention issue (yes - SQL Server will pick up on the invalid name issue), you can use a query like the one below (i fixed the names to make this work)
declare @x xml
set @x='<PartsList>
<a123>
<Color>blue</Color>
<Size>small</Size>
</a123>
<a124>
<Color>black</Color>
<Size>medium</Size>
</a124>
<a125>
<Color>red</Color>
<Size>large</Size>
</a125>
</PartsList>'
select c.value('local-name(.)','varchar(100)') colname,
c.value('(./Color)[1]','varchar(100)'),
c.value('(./Size)[1]','varchar(100)') size
from @x.nodes('/PartsList/*') vm(c)
EDIT: sry - I am looking at the wrong end of the issue - you're attempting to generate this - not parse it.
I am pretty sure this is possible using reasonably advanced FLOQWR statements, but it may take some time to get this right.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 19, 2012 at 3:24 pm
Here's a version of FLWOR which approximates what you want (again - with the name isssue handled)
declare @x xml
;with a as (
select 'a123' partID, 'blue' color,'large' size
union all
select 'a124' partID, 'green' color,'medium' size
union all
select 'a125' partID, 'red' color,'small' size)
select @x=(select partid as "@partid",color as "color", size from a for XML path ('part'), root('PartList'), type)
select cast(replace(replace(cast(
@x.query
('
for $a in PartList,
$e in $a/part
return <partlist>
{concat("<",string($e/@partid),">")}
{$e/*}
{concat("</",string($e/@partid),">")}
</partlist>
') as varchar(max)),'<','<'),'>','>') as XML)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 19, 2012 at 6:38 pm
Timothy Graffham (9/19/2012)
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?
OK, so now Matt and Eugene have shown that it can be done.
The question now becomes will you be remiss in your responsibilities and not at least question why someone is asking for a format that isn't standard XML?
"All that is necessary for the triumph of evil is that good men do nothing. -- Edmund Burke
The SQLverse version:
"All that is necessary for the triumph of bad database design is that good DBAs don't question. -- Dwain.C
You can quote me.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply