SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Stewart Campbell / XML to Relational / Latest Posts

RE: XML to Relational

Dineshbabu — Thu, 24 Jan 2013 05:42:12 GMT

I learnt something about XML, Discussions was very informative.

RE: XML to Relational

Toreador — Mon, 30 Jan 2012 03:25:57 GMT

[quote][b]L' Eomot Inversé (1/27/2012)[/b][hr]I have a question arising from the discussion: Why are references to parent nodes not liked?[/quote]I was going to ask the same thing. I do a lot of work with XML, and often reference parent nodes, so it would be good to know if I'm doing something wrong :-)

RE: XML to Relational

Revenant — Sat, 28 Jan 2012 16:51:52 GMT

I could not have ge this done during working hours, I had to take on it on Saturday and got it right.Thanks, Stewart!

RE: XML to Relational

sknox — Fri, 27 Jan 2012 12:44:00 GMT

[quote][b]L' Eomot Inversé (1/27/2012)[/b][hr][quote][b]sknox (1/27/2012)[/b][hr]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.[/quote]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.[/quote]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.

RE: XML to Relational

L' Eomot Inversé — Fri, 27 Jan 2012 12:42:11 GMT

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?

RE: XML to Relational

sknox — Fri, 27 Jan 2012 12:31:43 GMT

[quote][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?[/quote]Nope you're definitely not the only one.[quote]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.[/quote]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.

RE: XML to Relational

L' Eomot Inversé — Fri, 27 Jan 2012 12:31:29 GMT

[quote][b]sknox (1/27/2012)[/b][hr]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.[/quote]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.

RE: XML to Relational

SQLRNNR — Fri, 27 Jan 2012 10:10:53 GMT

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.

RE: XML to Relational

Mattrick — Fri, 27 Jan 2012 10:10:01 GMT

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

RE: XML to Relational

Chad Crawford — Fri, 27 Jan 2012 10:08:33 GMT

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.

RE: XML to Relational

OzYbOi d(-_-)b — Fri, 27 Jan 2012 09:05:36 GMT

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: :-Phappy friday and bring on the weekend! :-D

RE: XML to Relational

sknox — Fri, 27 Jan 2012 08:44:21 GMT

[quote][b]SQL Kiwi (1/26/2012)[/b][hr]...For anyone that wants to play with the XML, I think this is about right:[code="sql"]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>';[/code][/quote]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:[code]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>';[/code]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: [code]select t.c.value('../../@name','VARCHAR(20)') name, t.c.value('@name','VARCHAR(20)') componentname, t.c.value('@howMany','tinyint') numberOfItemsfrom @x.nodes('toys/myToy/components/part') t(c)[/code](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.

RE: XML to Relational

paul s-306273 — Fri, 27 Jan 2012 08:00:31 GMT

Lots of fuss and discussion about this question - isn't that a good thing?

RE: XML to Relational

mbova407 — Fri, 27 Jan 2012 07:51:47 GMT

I really like this question since I use a lot of XML for bulk inserts of business objects.And thank you to Paul and everyone, I just learned that Cross Apply method seems to be about 5x faster. Never though of that method.[code="sql"]DECLARE @x xmlSET @X =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>';SELECT [name] = T1.Toy.value('name[1]','VARCHAR(20)'), componentname = T2.Part.value('name[1]','VARCHAR(20)'), numberOfItems = T2.Part.value('howMany[1]','TINYINT')FROM @x.nodes('/toys/myToy') T1(Toy)CROSS APPLY T1.Toy.nodes('./components/part') T2(Part)SELECT [name] = T2.Part.value('../../name[1]','VARCHAR(20)'), componentname = T2.Part.value('name[1]','VARCHAR(20)'), numberOfItems = T2.Part.value('howMany[1]','TINYINT')FROM @x.nodes('/toys/myToy/components/part') T2(Part)[/code]

RE: XML to Relational

Brigadur — Fri, 27 Jan 2012 07:14:10 GMT

[quote][b]SQL Kiwi (1/27/2012)[/b]That's the same as the code I posted (just without the /text() optimization). [/quote]I don't understand what this /text() optimization does. On my system it makes the example query 4 times faster. Why is that? Does it performs better for any sql data type?

RE: XML to Relational

LuciaNewbie — Fri, 27 Jan 2012 06:27:05 GMT

Yes, I got it right!

RE: XML to Relational

Iulian -207023 — Fri, 27 Jan 2012 06:21:28 GMT

Thank you for the questionIulian

RE: XML to Relational

Britt Cluff — Fri, 27 Jan 2012 06:12:56 GMT

My logic was totally wrong. The reverse axis steps on the correct answer didn't seem right to me.

RE: XML to Relational

Thomas Abraham — Fri, 27 Jan 2012 05:28:22 GMT

[quote][b]SQL Kiwi (1/26/2012)[/b][hr]I dislike questions that make a parser/syntax checker out of the reader, especially where there is little in the way of learning value[/quote]As soon as I saw the question, I had the same reaction. By the time I finished the first pass through the code, I didn't care what the answer was. Glad I don't have to work with XML!

RE: XML to Relational

John Mitchell-245523 — Fri, 27 Jan 2012 04:31:30 GMT

[quote][b]Stewart "Arturius" Campbell (1/27/2012)[/b][hr][quote][b]KWymore (1/27/2012)[/b][hr]Stewart,One thing to note. Answer 2 and 3 don't match the case for columns "name" and "componentname" as declared in the example code. Since I know little of XML, I took a stab at this question by eliminating those 2 answers first based solely on the columns not matching and then tried to figure it out. I got lucky and answered correctly. Not sure how many others would narrow down their response this way but I thought I would bring it to your attention anyways.[/quote]Thanks for this. I have checked, and found that the only place where the column names differ is in the alias assignment of the relevant lines.The relevant element names, as used in the tab1.col1.value() method, are correct.[/quote]Yes, but the fact that the case used in the aliases is not consistent throughout means that answers 2 and 3 cannot possibly give the required result set. I also managed to get it right by eliminating those two and taking a guess.John

RE: XML to Relational

call.copse — Fri, 27 Jan 2012 04:23:26 GMT

It was a bit fiddly but then in my experience that is the case when dealing with xml, especially in SQL.

RE: XML to Relational

Brigadur — Fri, 27 Jan 2012 03:55:03 GMT

Very useful question, thanks. I learnt something about xml parsing and also optimization so far today :). Thanks for all the comments.I would also like to defend the question. I think seeing unconventional ways of solving a problem are always helpful. When the task is to review existing code, it is helpful to know what is working (although not optimal).Cheers

RE: XML to Relational

Stewart "Arturius" Campbell — Fri, 27 Jan 2012 02:15:30 GMT

[quote][b]KWymore (1/27/2012)[/b][hr]Stewart,One thing to note. Answer 2 and 3 don't match the case for columns "name" and "componentname" as declared in the example code. Since I know little of XML, I took a stab at this question by eliminating those 2 answers first based solely on the columns not matching and then tried to figure it out. I got lucky and answered correctly. Not sure how many others would narrow down their response this way but I thought I would bring it to your attention anyways.[/quote]Thanks for this. I have checked, and found that the only place where the column names differ is in the alias assignment of the relevant lines.The relevant element names, as used in the tab1.col1.value() method, are correct.

RE: XML to Relational

KWymore — Fri, 27 Jan 2012 01:55:45 GMT

Stewart,One thing to note. Answer 2 and 3 don't match the case for columns "name" and "componentname" as declared in the example code. Since I know little of XML, I took a stab at this question by eliminating those 2 answers first based solely on the columns not matching and then tried to figure it out. I got lucky and answered correctly. Not sure how many others would narrow down their response this way but I thought I would bring it to your attention anyways.

RE: XML to Relational

Stewart "Arturius" Campbell — Fri, 27 Jan 2012 01:52:21 GMT

Thanks for the optimisation hint, Paul. I will definitely be investigating the impact of applying this to the XML used in our environment (which is quite extensive).So the question poser learns from another...

RE: XML to Relational

Hugo Kornelis — Fri, 27 Jan 2012 01:40:12 GMT

I knew I'd probably get this wrong, as I know very little about XML, and I'm not actively seeking to change that. But from what I picked up over time and some logical thinking, I managed to eliminate three of the four answer options.Unfortunately, logical thinking is not always a guarantee for correctness. I rejected the correct answer because of the reverse axis steps. I expected them to be disallowed. Pauls reply taught me that this is not recommended practice, so my logical feeling was not too far off...If the version Paul posted had been included in the answers, I would have spotted the difference with the answer I chose (b), and I would probably have come to the correct conclusion.Ah well. No point for me today, but at least I got the reassurance that my limited knowledge and gut feelings about XML were not [i]that[/i] far off.Oh, and thanks Paul for the extra nugget about the /text() optimization - if I even do get a need to handle XML, I'll try to remember that!

RE: XML to Relational

Paul White — Fri, 27 Jan 2012 00:40:46 GMT

[quote][b]Stewart "Arturius" Campbell (1/27/2012)[/b][hr]The aim was to illuminate on a badly documented subject within MSSQL, not "trick" people.[/quote]Benefit of the doubt then.[quote]Another possiblity for a right answer could have been:[code="sql"]SELECT [name] = T1.Toy.value('name[1]','VARCHAR(20)'), componentname = T2.Part.value('name[1]','VARCHAR(20)'), numberOfItems = T2.Part.value('howMany[1]','TINYINT')FROM @x.nodes('/toys/myToy') T1(Toy)CROSS APPLY T1.Toy.nodes('./components/part') T2(Part)[/code][/quote]That's the same as the code I posted (just without the /text() optimization). Query plans below:Query above:[img]http://www.sqlservercentral.com/Forums/Attachment10729.aspx[/img]Previous:[img]http://www.sqlservercentral.com/Forums/Attachment10730.aspx[/img]

RE: XML to Relational

Koen Verbeeck — Fri, 27 Jan 2012 00:28:46 GMT

[quote][b]Stewart "Arturius" Campbell (1/27/2012)[/b][hr][quote][b]SQL Kiwi (1/26/2012)[/b][hr]Hiding a cross join in code that is closer to the 'right' solution makes me wonder if the author was more concerned with achieving a low '% correct' figure that teaching the reader anything about XQuery or XML. [/quote]The aim was to illuminate on a badly documented subject within MSSQL, not "trick" people.Another possiblity for a right answer could have been:[code="sql"]SELECT [name] = T1.Toy.value('name[1]','VARCHAR(20)'), componentname = T2.Part.value('name[1]','VARCHAR(20)'), numberOfItems = T2.Part.value('howMany[1]','TINYINT')FROM @x.nodes('/toys/myToy') T1(Toy)CROSS APPLY T1.Toy.nodes('./components/part') T2(Part)[/code][/quote]I like this answer better, as it is more readable (personal opinion of course).

RE: XML to Relational

Stewart "Arturius" Campbell — Fri, 27 Jan 2012 00:21:09 GMT

[quote][b]SQL Kiwi (1/26/2012)[/b][hr]Hiding a cross join in code that is closer to the 'right' solution makes me wonder if the author was more concerned with achieving a low '% correct' figure that teaching the reader anything about XQuery or XML. [/quote]The aim was to illuminate on a badly documented subject within MSSQL, not "trick" people.Another possiblity for a right answer could have been:[code="sql"]SELECT [name] = T1.Toy.value('name[1]','VARCHAR(20)'), componentname = T2.Part.value('name[1]','VARCHAR(20)'), numberOfItems = T2.Part.value('howMany[1]','TINYINT')FROM @x.nodes('/toys/myToy') T1(Toy)CROSS APPLY T1.Toy.nodes('./components/part') T2(Part)[/code]

RE: XML to Relational

Koen Verbeeck — Thu, 26 Jan 2012 23:13:22 GMT

Buh, got it wrong. But that's because I refuse to learn XQuery :-D

RE: XML to Relational

Paul White — Thu, 26 Jan 2012 21:58:01 GMT

I dislike questions that make a parser/syntax checker out of the reader, especially where there is little in the way of learning value.It is sad the 'correct' answer includes reverse axis steps ('../') since these are best avoided in practice. Hiding a cross join in code that is closer to the 'right' solution makes me wonder if the author was more concerned with achieving a low '% correct' figure that teaching the reader anything about XQuery or XML. I would have expected the correct answer to be closer to this:[code="sql"]SELECT name = tab1.col1.value('(./name/text())[1]', 'varchar(20)'), componentName = tab1.col1.value('(./name/text())[1]', 'varchar(20)'), numberOfItems = tab1.col1.value('(./howMany/text())[1]', 'tinyint')FROM @x.nodes('toys/myToy') tab (col)CROSS APPLY tab.col.nodes('./components/part') AS tab1 (col1);[/code]For anyone that wants to play with the XML, I think this is about right:[code="sql"]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>';[/code]

XML to Relational

Stewart "Arturius" Campbell — Thu, 26 Jan 2012 21:36:08 GMT

Comments posted to this topic are about the item [B]XML to Relational[/B]