SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML to Relational


XML to Relational

Author
Message
sknox
sknox
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3898 Visits: 2920
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.
OzYbOi d(-_-)b
OzYbOi d(-_-)b
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1686 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 w00t :-P

happy friday and bring on the weekend! :-D
Chad Crawford
 Chad Crawford
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4227 Visits: 18732
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.
Mattrick
Mattrick
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 767
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
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63617 Visits: 18570
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

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25100 Visits: 12484
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

sknox
sknox
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3898 Visits: 2920
[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.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25100 Visits: 12484
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

sknox
sknox
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3898 Visits: 2920
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.
Revenant
Revenant
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12161 Visits: 5010
I could not have ge this done during working hours, I had to take on it on Saturday and got it right.

Thanks, Stewart!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search