September 20, 2012 at 5:49 am
I do have to agree with the byte-bloat issue.
Can anyone beat this? its from the NHS clinical data set upload for patient details
<personGenderCodeCurrent>M</personGenderCodeCurrent>
September 20, 2012 at 6:00 am
aaron.reese (9/20/2012)
I do have to agree with the byte-bloat issue.
Can anyone beat this? its from the NHS clinical data set upload for patient details
<personGenderCodeCurrent>M</personGenderCodeCurrent>
They must be doing those special sex-change operations!;-)
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 20, 2012 at 7:27 am
aaron.reese (9/20/2012)
I do have to agree with the byte-bloat issue.
Can anyone beat this? its from the NHS clinical data set upload for patient details
<personGenderCodeCurrent>M</personGenderCodeCurrent>
There is nothing wrong with that!
XML is designed specifically for this.
September 20, 2012 at 8:40 am
Eugene Elutin (9/20/2012)
aaron.reese (9/20/2012)
I do have to agree with the byte-bloat issue.
Can anyone beat this? its from the NHS clinical data set upload for patient details
<personGenderCodeCurrent>M</personGenderCodeCurrent>There is nothing wrong with that!
XML is designed specifically for this.
For gender transformations?
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 20, 2012 at 10:30 am
dwain.c (9/20/2012)
Eugene Elutin (9/20/2012)
aaron.reese (9/20/2012)
I do have to agree with the byte-bloat issue.
Can anyone beat this? its from the NHS clinical data set upload for patient details
<personGenderCodeCurrent>M</personGenderCodeCurrent>There is nothing wrong with that!
XML is designed specifically for this.
For gender transformations?
No, that is what XSLT is for.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 20, 2012 at 10:34 am
dwain.c (9/20/2012)
Eugene Elutin (9/20/2012)
aaron.reese (9/20/2012)
I do have to agree with the byte-bloat issue.
Can anyone beat this? its from the NHS clinical data set upload for patient details
<personGenderCodeCurrent>M</personGenderCodeCurrent>There is nothing wrong with that!
XML is designed specifically for this.
For gender transformations?
Why not, at the end? It is possible! So very thoughtful design... :hehe:
September 21, 2012 at 6:49 am
opc.three (9/19/2012)
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 😉
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.
September 21, 2012 at 7:03 am
tim_harkin (9/21/2012)
opc.three (9/19/2012)
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 😉
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.
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 21, 2012 at 8:59 am
opc.three (9/21/2012)
tim_harkin (9/21/2012)
opc.three (9/19/2012)
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 😉
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.
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.
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 typos
----------------------------------------------------------------------------------
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 21, 2012 at 9:14 am
Matt Miller (#4) (9/21/2012)
opc.three (9/21/2012)
tim_harkin (9/21/2012)
opc.three (9/19/2012)
Jeff Moden (9/18/2012)...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.
Well said.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 3, 2012 at 6:50 am
Edited to remove unneeded message.
October 21, 2012 at 5:47 pm
opc.three (9/21/2012)
Matt Miller (#4) (9/21/2012)
opc.three (9/21/2012)
tim_harkin (9/21/2012)
opc.three (9/19/2012)
Jeff Moden (9/18/2012)...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.
Well said.
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.
--Jeff Moden
October 21, 2012 at 6:52 pm
On second thought...response removed
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
October 22, 2012 at 4:18 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?
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.
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
<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>
Properly formatted XML examples
/* 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
<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>
/* 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
<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>
/* 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 @TempTable
FOR XML PATH('Part'), ROOT('PartsList')
)
SELECT @x AS XML_Result
<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>
/* 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
<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>
October 22, 2012 at 7:04 pm
Timothy Graffham (9/18/2012)
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
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?
--Jeff Moden
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply