InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 09:39:38 GMT20http://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]Timothy Graffham (9/18/2012)[/b][hr]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 Size123 blue small124 black medium125 red largeI 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[/quote]Hi Tim,Now that Mr. Willis has given you some pretty cool answers, I wonder if you'd mind answering a question for me. Is this data for consumption by a GUI or is it for a simple data transfer? If for data transfer, is the receiving end another SQL Server?Mon, 22 Oct 2012 19:04:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]Timothy Graffham (9/19/2012)[/b][hr]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?[/quote]It is possible to display your data in the manner you are seeking as a STRING that will look like XML but will not be compliant and will not render in SSMS. The method to get the result you requested requires some messy replaces since normal XML methods won't do it. If that will work for you then my example is below. Just in case you have some flexibility or need the data in XML-compliant format I've also provided a few formatting variations so you can see how they might be done.[code="sql"]DECLARE @x XML ,@s VARCHAR(MAX)DECLARE @TempTable TABLE ( ID INT IDENTITY(1,1) NOT NULL ,Part VARCHAR(10) ,Color VARCHAR(10) ,Size VARCHAR(10) ) INSERT INTO @TempTable SELECT '123','blue','small' UNION ALL SELECT '124','black','medium' UNION ALL SELECT '125','red','large' /* Note: The part number tag formatted as '<123>' is an invalid XML tag *//* and SQL will not render. This will create a pseudo-XML string using the *//* integer part number as tag (non-XML compliant) */SET @x = ( SELECT CAST(Part AS VARCHAR(50)) AS 'StartPart' ,(SELECT temp.Color AS 'Color' ,temp.Size AS 'Size' ,CAST(Part AS VARCHAR(50)) AS 'EndPart' FOR XML PATH(''),TYPE ) FROM @TempTable AS temp FOR XML PATH(''), ROOT('PartsList') ) SET @s = CONVERT(VARCHAR(MAX), @x)SET @s = REPLACE( REPLACE( REPLACE( REPLACE(@s,'</StartPart>','>') ,'<StartPart>','<') ,'</EndPart>','>') ,'<EndPart>','</') SELECT @s AS String_Result[/code][code="xml"]<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>[/code]Properly formatted XML examples[code="sql"]/* Adds ascending character value to part number to create *//* valid XML tag and sets the part number as its own parent tag *//* with 'Color' and 'Size' as child tags. */SET @x = ( SELECT CAST(CHAR(temp.ID+64) AS VARCHAR(3)) +CAST(Part AS VARCHAR(50)) AS 'StartPart' ,(SELECT temp.Color AS 'Color' ,temp.Size AS 'Size' ,CAST(CHAR(temp.ID+64) AS VARCHAR(3)) +CAST(Part AS VARCHAR(50)) AS 'EndPart' FOR XML PATH(''),TYPE ) FROM @TempTable AS temp FOR XML PATH(''), ROOT('PartsList') ) SET @s = CONVERT(VARCHAR(MAX), @x)SET @s = REPLACE( REPLACE( REPLACE( REPLACE(@s,'</StartPart>','>') ,'<StartPart>','<') ,'</EndPart>','>') ,'<EndPart>','</')SET @x = CONVERT(XML, @s)SELECT @x AS XML_Result[/code][code="xml"]<PartsList> <A123> <Color>blue</Color> <Size>small</Size> </A123> <B124> <Color>black</Color> <Size>medium</Size> </B124> <C125> <Color>red</Color> <Size>large</Size> </C125></PartsList>[/code][code="sql"] /* Sets part number as single un-nested 'Part' tag which is *//* followed sequentially by 'Color' and 'Size' for each part number */ SET @x = ( SELECT CAST(Part AS VARCHAR(50)) AS 'Part' ,(SELECT temp.Color AS 'Color' ,temp.Size AS 'Size' FOR XML PATH(''),TYPE ) FROM @TempTable AS temp FOR XML PATH(''), ROOT('PartsList') )SELECT @x AS XML_Result[/code][code="xml"]<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>[/code][code="sql"]/* Sets parent 'Part' tag with 'Color' and 'Size' as child tags */SET @x = (SELECT (SELECT ISNULL (CAST(Part AS VARCHAR (50)),'') AS 'Number' FOR XML PATH(''), TYPE) ,(SELECT ISNULL (CAST(Color AS VARCHAR (50)),'') AS 'Color' FOR XML PATH(''), TYPE) ,(SELECT ISNULL (CAST(Size AS VARCHAR (50)),'') AS 'Size' FOR XML PATH('') ,TYPE)FROM @TempTableFOR XML PATH('Part'), ROOT('PartsList'))SELECT @x AS XML_Result[/code][code="xml"]<PartsList> <Part> <Number>123</Number> <Color>blue</Color> <Size>small</Size> </Part> <Part> <Number>124</Number> <Color>black</Color> <Size>medium</Size> </Part> <Part> <Number>125</Number> <Color>red</Color> <Size>large</Size> </Part></PartsList>[/code][code="sql"]/* Sets parent 'Part' tag with a property value 'number=[part number]' *//* with 'Color' and 'Size' as child tags */SET @x = (SELECT ( SELECT Part AS 'Part/@number' ,Color AS 'Part/Color' ,Size AS 'Part/Size' FOR XML PATH(''), TYPE ) FROM @TempTable FOR XML PATH(''), ROOT('PartsList'))SELECT @x AS XML_Result[/code][code="xml"]<PartsList> <Part number="123"> <Color>blue</Color> <Size>small</Size> </Part> <Part number="124"> <Color>black</Color> <Size>medium</Size> </Part> <Part number="125"> <Color>red</Color> <Size>large</Size> </Part></PartsList>[/code]Mon, 22 Oct 2012 16:18:56 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspxOn second thought...response removedSun, 21 Oct 2012 18:52:02 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]opc.three (9/21/2012)[/b][hr][quote][b]Matt Miller (#4) (9/21/2012)[/b][hr][quote][b]opc.three (9/21/2012)[/b][hr][b]tim_harkin (9/21/2012)[/b][hr][b]opc.three (9/19/2012)[/b][hr][b]Jeff Moden (9/18/2012)[/b][hr][/quote]...truncated for brevity...I'd also add in that the markup isn't purely for presentation purposes. XML comes with a lot of tooling that can be very useful, so thinking of it only as a file structure isn't give it a fair shake. If you actually use XML, XSD structures XPATH and XQUERY just ot name a few you can reprsent much more complex structures (and much fancier relations) than you can with CSV, validate that your data coming back will actually have a chance to meet your data requirements on the way back in, and "delegate" some of the simple data interrogation exercise to the external application without having to continuously pester the underlying database.The XML toolset frankly allows you to extend the DBA concerns around DRI, data typing, and constraints around the data into the App layer without requiring the DBA to have their boot on the dev's neck while they develop.With CSV vs XML, you're essentially trading size efficiency and limited ability to represent relational data for less space efficiency and much better relational capability. To me it's definitely apples and oranges. If your relations are complex, it's rather easy for the CSV representation to become MORE bloated than the XML.[/quote]Well said.[/quote]Well said, indeed. I totally disagree but well said. Just as with XML, if you know how to actually use delimited data, all the DRI, data typing, constaints, etc, are quite easy to achieve. As for some who's worried about the DBA having a boot on the dev's neck while they develop, it's probably because they're trying to do something wrong and the DBA should be concerned.Sun, 21 Oct 2012 17:47:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspxEdited to remove unneeded message.Wed, 03 Oct 2012 06:50:35 GMTcoreyfurmanhttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]Matt Miller (#4) (9/21/2012)[/b][hr][quote][b]opc.three (9/21/2012)[/b][hr][b]tim_harkin (9/21/2012)[/b][hr][b]opc.three (9/19/2012)[/b][hr][b]Jeff Moden (9/18/2012)[/b][hr][/quote]...truncated for brevity...I'd also add in that the markup isn't purely for presentation purposes. XML comes with a lot of tooling that can be very useful, so thinking of it only as a file structure isn't give it a fair shake. If you actually use XML, XSD structures XPATH and XQUERY just ot name a few you can reprsent much more complex structures (and much fancier relations) than you can with CSV, validate that your data coming back will actually have a chance to meet your data requirements on the way back in, and "delegate" some of the simple data interrogation exercise to the external application without having to continuously pester the underlying database.The XML toolset frankly allows you to extend the DBA concerns around DRI, data typing, and constraints around the data into the App layer without requiring the DBA to have their boot on the dev's neck while they develop.With CSV vs XML, you're essentially trading size efficiency and limited ability to represent relational data for less space efficiency and much better relational capability. To me it's definitely apples and oranges. If your relations are complex, it's rather easy for the CSV representation to become MORE bloated than the XML.[/quote]Well said.Fri, 21 Sep 2012 09:14:13 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]opc.three (9/21/2012)[/b][hr][quote][b]tim_harkin (9/21/2012)[/b][hr][quote][b]opc.three (9/19/2012)[/b][hr][quote][b]Jeff Moden (9/18/2012)[/b][hr]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.[/quote]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 ;-)[/quote]Apples and oranges. For storing and transferring data, XML is bloated. The architects of the internet are not concerned with data storage - they are concerned with presentation. So store your data w\o the bloat, and when it is rendered, add the markup. Instead of storing the markup redundantly.[/quote]My example is contrived, but familiar so serves the point that modeling complex types in flat files is an excercise in futility. XML is sometimes the better choice even for file-based transfers. My underlying point was, choose the right tool for the job.[/quote]I'd also add in that the markup isn't purely for presentation purposes. XML comes with a lot of tooling that can be very useful, so thinking of it only as a file structure isn't giving it a fair shake. If you actually use XML, XSD structures, XPATH and XQUERY just to name a few, you can represent much more complex structures (and much fancier relations) than you can with CSV. You can also validate that your data coming back will actually have a chance to meet your data requirements on the way back in, and "delegate" some of the simple data interrogation exercise to the external application without having to continuously pester the underlying database.The XML toolset frankly allows you to extend the DBA concerns around DRI, data typing, and constraints around the data into the App layer, without requiring the DBA to have their boot on the dev's neck while they develop.With CSV vs XML, you're essentially trading size efficiency and limited ability to represent relational data for less space efficiency and much better relational capability. To me it's definitely apples and oranges. If your relations are complex, it's rather easy for the CSV representation to become MORE bloated than the XML.EDIT: fixed a few typosFri, 21 Sep 2012 08:59:36 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]tim_harkin (9/21/2012)[/b][hr][quote][b]opc.three (9/19/2012)[/b][hr][quote][b]Jeff Moden (9/18/2012)[/b][hr]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.[/quote]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 ;-)[/quote]Apples and oranges. For storing and transferring data, XML is bloated. The architects of the internet are not concerned with data storage - they are concerned with presentation. So store your data w\o the bloat, and when it is rendered, add the markup. Instead of storing the markup redundantly.[/quote]My example is contrived, but familiar so serves the point that modeling complex types in flat files is an excercise in futility. XML is sometimes the better choice even for file-based transfers. My underlying point was, choose the right tool for the job.Fri, 21 Sep 2012 07:03:24 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]opc.three (9/19/2012)[/b][hr][quote][b]Jeff Moden (9/18/2012)[/b][hr]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.[/quote]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 ;-)[/quote]Apples and oranges. For storing and transferring data, XML is bloated. The architects of the internet are not concerned with data storage - they are concerned with presentation. So store your data w\o the bloat, and when it is rendered, add the markup. Instead of storing the markup redundantly.Fri, 21 Sep 2012 06:49:38 GMTtim_harkinhttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]dwain.c (9/20/2012)[/b][hr][quote][b]Eugene Elutin (9/20/2012)[/b][hr][quote][b]aaron.reese (9/20/2012)[/b][hr][quote]I do have to agree with the byte-bloat issue.[/quote]Can anyone beat this? its from the NHS clinical data set upload for patient details[code="xml"]<personGenderCodeCurrent>M</personGenderCodeCurrent>[/code][/quote]There is nothing wrong with that!XML is designed specifically for this.[/quote]For gender transformations?[/quote]Why not, at the end? It is possible! So very thoughtful design... :hehe:Thu, 20 Sep 2012 10:34:45 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]dwain.c (9/20/2012)[/b][hr][quote][b]Eugene Elutin (9/20/2012)[/b][hr][quote][b]aaron.reese (9/20/2012)[/b][hr][quote]I do have to agree with the byte-bloat issue.[/quote]Can anyone beat this? its from the NHS clinical data set upload for patient details[code="xml"]<personGenderCodeCurrent>M</personGenderCodeCurrent>[/code][/quote]There is nothing wrong with that!XML is designed specifically for this.[/quote]For gender transformations?[/quote]No, that is what [u][url=http://en.wikipedia.org/wiki/XSLT]XSLT[/url][/u] is for.Thu, 20 Sep 2012 10:30:06 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]Eugene Elutin (9/20/2012)[/b][hr][quote][b]aaron.reese (9/20/2012)[/b][hr][quote]I do have to agree with the byte-bloat issue.[/quote]Can anyone beat this? its from the NHS clinical data set upload for patient details[code="xml"]<personGenderCodeCurrent>M</personGenderCodeCurrent>[/code][/quote]There is nothing wrong with that!XML is designed specifically for this.[/quote]For gender transformations?Thu, 20 Sep 2012 08:40:25 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]aaron.reese (9/20/2012)[/b][hr][quote]I do have to agree with the byte-bloat issue.[/quote]Can anyone beat this? its from the NHS clinical data set upload for patient details[code="xml"]<personGenderCodeCurrent>M</personGenderCodeCurrent>[/code][/quote]There is nothing wrong with that!XML is designed specifically for this.Thu, 20 Sep 2012 07:27:13 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]aaron.reese (9/20/2012)[/b][hr][quote]I do have to agree with the byte-bloat issue.[/quote]Can anyone beat this? its from the NHS clinical data set upload for patient details[code="xml"]<personGenderCodeCurrent>M</personGenderCodeCurrent>[/code][/quote]They must be doing those special sex-change operations!;-)Thu, 20 Sep 2012 06:00:43 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote]I do have to agree with the byte-bloat issue.[/quote]Can anyone beat this? its from the NHS clinical data set upload for patient details[code="xml"]<personGenderCodeCurrent>M</personGenderCodeCurrent>[/code]Thu, 20 Sep 2012 05:49:56 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]Timothy Graffham (9/19/2012)[/b][hr]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?[/quote]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?[i]"All that is necessary for the triumph of evil is that good men do nothing.[/i] -- Edmund BurkeThe SQLverse version:[i]"All that is necessary for the triumph of bad database design is that good DBAs don't question.[/i] -- Dwain.CYou can quote me.Wed, 19 Sep 2012 18:38:31 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspxHere's a version of FLWOR which approximates what you want (again - with the name isssue handled)[code="sql"]declare @x xml;with a as (select 'a123' partID, 'blue' color,'large' sizeunion allselect 'a124' partID, 'green' color,'medium' sizeunion allselect '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)[/code]Wed, 19 Sep 2012 15:24:51 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspxAssuming 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)[code="sql"]declare @x xmlset @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) [/code]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.Wed, 19 Sep 2012 13:32:07 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote]...I want the xml to look like this:<PartsList> <123> ...[/quote]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[url]http://www.w3schools.com/xml/xml_elements.asp[/url]But if you still want it as non-XML text, you can do the following with Dwain solution:[code="sql"]DECLARE @t TABLE (Part INT, Color VARCHAR(10), Size VARCHAR(10))INSERT INTO @tSELECT 123,'blue','small'UNION ALL SELECT 124,'black','medium'UNION ALL SELECT 125,'red','large'select * from @tDECLARE @xml VARCHAR(max)SELECT @xml = (SELECT Part, Color, Size, '/' + cast(Part as varchar) AS PartCFROM @tFOR XML PATH(''), ROOT('PartsList') )SELECT REPLACE(REPLACE(REPLACE(REPLACE( @xml,'</Part>','>'),'<Part>','<'),'</PartC>','>'),'<PartC>','<') [/code]Please Note, you will not be able to load above text to DOM object for use as XML...Wed, 19 Sep 2012 12:04:05 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]Timothy Graffham (9/19/2012)[/b][hr]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?[/quote]All fair points. These forums are in a 'discussion format' as opposed to a 'Q&A format' like [u][url=http://ask.sqlservercentral.com]http://ask.sqlservercentral.com[/url][/u] 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: [u][url=http://stackoverflow.com/questions/3240297/how-do-i-use-column-values-as-xml-element-names-using-for-xml-in-sql-server-2005]http://stackoverflow.com/questions/3240297/how-do-i-use-column-values-as-xml-element-names-using-for-xml-in-sql-server-2005[/url][/u]Wed, 19 Sep 2012 11:39:18 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspxI 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?Wed, 19 Sep 2012 10:32:12 GMTTimothy Graffhamhttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]Jeff Moden (9/18/2012)[/b][hr]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.[/quote]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 ;-)Wed, 19 Sep 2012 08:06:19 GMTopc.threehttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]Jason-299789 (9/19/2012)[/b][hr]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. [/quote]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.Wed, 19 Sep 2012 07:12:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspxThe 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. Wed, 19 Sep 2012 01:47:25 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]Jeff Moden (9/18/2012)[/b][hr]As a side bar, this is one of the reasons for my strong dislike for XML.[/quote]As a side bar, here is one of the reasons I shouldn't be allowed to write SQL that involves XML:[url]http://www.sqlservercentral.com/Forums/Topic1359470-1292-2.aspx#bm1360618[/url]:-PWed, 19 Sep 2012 01:23:57 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspxConsider ASCII characters 28 through 31 and you don't even have to worry about embedded elemental columns.Tue, 18 Sep 2012 21:06:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspx[quote][b]Jeff Moden (9/18/2012)[/b][hr]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.[code="plain"]<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>[/code]The following only takes 61 bytes and has all the same information in it.[code="sql"]Part,Color,Size123,blue,small124,black,medium125,red,large[/code]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.[/quote]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:Tue, 18 Sep 2012 20:55:34 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspxAs 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.[code="plain"]<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>[/code]The following only takes 61 bytes and has all the same information in it.[code="sql"]Part,Color,Size123,blue,small124,black,medium125,red,large[/code]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.Tue, 18 Sep 2012 20:42:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspxI think you're going to have a problem converting your part number into a tag.You can do something like this:[code="sql"]DECLARE @t TABLE (Part INT, Color VARCHAR(10), Size VARCHAR(10))INSERT INTO @tSELECT 123,'blue','small'UNION ALL SELECT 124,'black','medium'UNION ALL SELECT 125,'red','large'SELECT Part, Color, SizeFROM @tFOR XML PATH(''), ROOT('PartsList')[/code]Which gives you this:[code="plain"]<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>[/code]The above should be relatively easy to shred. Let me know if you need help with that (assuming it works for you).Tue, 18 Sep 2012 18:32:00 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1361033-392-1.aspxI'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 Size123 blue small124 black medium125 red largeI 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,TimTue, 18 Sep 2012 15:09:17 GMTTimothy Graffham