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 «««1234»»

XML to Relational Expand / Collapse
Author
Message
Posted Friday, January 27, 2012 8:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:20 AM
Points: 1,339, Visits: 1,715
SQL Kiwi (1/26/2012)
...
For anyone that wants to play with the XML, I think this is about right:

DECLARE @x xml =
N'<toys>
<myToy>
<name>Train</name>
<components>
<part><name>engine</name><howMany>1</howMany></part>
<part><name>coaches</name><howMany>10</howMany></part>
<part><name>caboose</name><howMany>1</howMany></part>
<part><name>tracks</name><howMany>125</howMany></part>
<part><name>switches</name><howMany>8</howMany></part>
<part><name>power controller</name><howMany>1</howMany></part>
</components>
</myToy>
<myToy>
<name>remote control car</name>
<components>
<part><name>remote control</name><howMany>1</howMany></part>
<part><name>car</name><howMany>1</howMany></part>
<part><name>batteries</name><howMany>8</howMany></part>
</components>
</myToy>
</toys>
';


Right as in accurate yes. Right as in correct is a matter of opinion. Elements should not be used for properties that occur 0 or 1 times and are expressible as attributes. In my opinion, a more correct form for this XML would be:
DECLARE @x xml =
N'<toys>
<myToy name="Train">
<components>
<part name="engine" howMany="1" />
<part name="coaches" howMany="10" />
<part name="caboose" howMany="1" />
<part name="tracks" howMany="125" />
<part name="switches" howMany="8" />
<part name="power controller" howMany="1" />
</components>
</myToy>
<myToy name="remote control car">
<components>
<part name="remote control" howMany="1" />
<part name="car" howMany="1" />
<part name="batteries" howMany="8" />
</components>
</myToy>
</toys>
';

Note how much shorter (464 instead of 636 characters, not counting whitespace) and cleaner that XML is. It's the unrelenting insistence of some to express every property in XML as an element that leads to XML being widely considered a waste of space and difficult to read. It doesn't have to be either.

(Of course, the SQL would have to be modified to address attributes instead of elements, e.g:
select 
t.c.value('../../@name','VARCHAR(20)') name,
t.c.value('@name','VARCHAR(20)') componentname,
t.c.value('@howMany','tinyint') numberOfItems
from @x.nodes('toys/myToy/components/part') t(c)

(edit: as a side note, running this query on the attribute-based XML in the same batch as the comparable query on the element-heavy XML and returning the query plan shows that the attribute-based XML query used 28% of the query cost, vs 72% for the element-heavy XML -- so it appears to be more efficient as well (tested on 2005 64-bit developer edition))
)

Okay, I'm done ranting.
Post #1242920
Posted Friday, January 27, 2012 9:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
thanks everyone for the discussion this morning.

nothing more that I can add to this discussion, other than to say it obviously insn't real world...

component lists are incomplete - when was the last time you got toys for the littles that didn't inlcude those bloody parts that have no apparent use

happy friday and bring on the weekend!
Post #1242940
Posted Friday, January 27, 2012 10:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:36 PM
Points: 2,393, Visits: 17,965
I liked the question and it didn't feel like a syntax parser. I believe all of the answers return results, but only one returns the result you really want, so I'm not checking syntax as much as leveraging my understanding of node depth and CROSS JOIN. Having some, but not a lot of experience in XML, I could see myself working through queries exactly like these trying to figure out how to worm what I wanted out of a piece of XML. It was nice to get the optimization and best practice notes here in the comments too since I don't get into XML frequently and never would have known how to optimize it.
Post #1243005
Posted Friday, January 27, 2012 10:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 1:32 PM
Points: 581, Visits: 733
First, I enjoyed the question. I do not know nearly enough about XQuery, and I have been trying to learn more. Thanks.

<rant>
Am I the only person that is frustrated when questions are posted as a picture, instead of text that can be copied and pasted for further experimentation?

I know there are some that want to discourage "cheating" on the QOTD, but I would think accessible scripts would be a better way to learn something. True, one could type this question up, and thank you Paul for doing that, but I would rather have these question, especially longer and more esoteric questions such as this one, be accessible by default.
</rant>

Despite my rant, I do thank you for the question, and the discussion it has created.

Have a good one,

Matt
Post #1243007
Posted Friday, January 27, 2012 10:10 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 17,822, Visits: 15,746
This is one of those questions that I hope a lot of people will read the discussion thread. The discussion is far more informative in this case.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1243009
Posted Friday, January 27, 2012 12:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 7,799, Visits: 9,548
sknox (1/27/2012)
It's the unrelenting insistence of some to express every property in XML as an element that leads to XML being widely considered a waste of space and difficult to read. It doesn't have to be either.

Well, yes, it is nonsense to insist that attributes should be expressed as singleton elements. And the space and processing saved by not doing so is valuable.
But even XML without that pointless extra verbosity is difficult to read and wastes time. (Yes, I'm an XML-hater; or rather someone who hates to see XML used for something for which it is utterly inappropriate, which happens often enough to make me inclined to hate first and looks and see if it was justified afterwards.


Tom
Post #1243095
Posted Friday, January 27, 2012 12:31 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:20 AM
Points: 1,339, Visits: 1,715
[b]Am I the only person that is frustrated when questions are posted as a picture, instead of text that can be copied and pasted for further experimentation?

Nope you're definitely not the only one.


I know there are some that want to discourage "cheating" on the QOTD, but I would think accessible scripts would be a better way to learn something.

Perhaps if there were a way to add the actual scripts in the answer section? Then you'd have to answer the question before you could get at them.

Of course, since the answer to yesterday's QotD is in today's e-mail, cheaters could simply wait a day to cheat, so the pictures don't really stop anything.
Post #1243096
Posted Friday, January 27, 2012 12:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 7,799, Visits: 9,548
Interesting question.

My dislike of XML in most of its manifestations (and especially of the hype of XML as a universal panacea) has ensured that I have learnt practically nothing about how XML in T-SQL works. But I found I could get this one right by counting rows - two options deliver too many rows, one delivers too few. Since three of teh four optons were wrong, the other must be the right one. Well, I thought I had counted the rows right, but I don't understand this T-SQL-XML stuff well enough to be at all sure. So when I clicked on my choice and discovered it was right I was not altogether unsurprised, and quite pleased.

I have a question arising from the discussion: Why are references to parent nodes not liked?


Tom
Post #1243103
Posted Friday, January 27, 2012 12:44 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:20 AM
Points: 1,339, Visits: 1,715
L' Eomot Inversé (1/27/2012)
sknox (1/27/2012)
It's the unrelenting insistence of some to express every property in XML as an element that leads to XML being widely considered a waste of space and difficult to read. It doesn't have to be either.

Well, yes, it is nonsense to insist that attributes should be expressed as singleton elements. And the space and processing saved by not doing so is valuable.
But even XML without that pointless extra verbosity is difficult to read and wastes time. (Yes, I'm an XML-hater; or rather someone who hates to see XML used for something for which it is utterly inappropriate, which happens often enough to make me inclined to hate first and looks and see if it was justified afterwards.


I certainly agree that XML is often used for things that it's not designed for.

I don't know about "difficult to read", though. I'd much rather read a well-formatted* XML document than what passes for a data dictionary in many shops nowadays.

*and since any well-formed XML document can easily be translated into a well-formatted one by many tools out there (IE if you have nothing else), if you don't have well-formatted XML, you don't really have XML.
Post #1243106
Posted Saturday, January 28, 2012 4:51 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 10:48 AM
Points: 4,126, Visits: 3,427
I could not have ge this done during working hours, I had to take on it on Saturday and got it right.

Thanks, Stewart!
Post #1243240
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse