Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML to Relational


XML to Relational

Author
Message
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6386 Visits: 7193
Comments posted to this topic are about the item XML to Relational

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11234 Visits: 11354
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:


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);


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>
';





Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18817 Visits: 13250
Buh, got it wrong. But that's because I refuse to learn XQuery :-D


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6386 Visits: 7193
SQL Kiwi (1/26/2012)
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.


The aim was to illuminate on a badly documented subject within MSSQL, not "trick" people.

Another possiblity for a right answer could have been:
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)



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18817 Visits: 13250
Stewart "Arturius" Campbell (1/27/2012)
SQL Kiwi (1/26/2012)
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.


The aim was to illuminate on a badly documented subject within MSSQL, not "trick" people.

Another possiblity for a right answer could have been:
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)



I like this answer better, as it is more readable (personal opinion of course).


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Paul White
Paul White
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11234 Visits: 11354
Stewart "Arturius" Campbell (1/27/2012)
The aim was to illuminate on a badly documented subject within MSSQL, not "trick" people.

Benefit of the doubt then.

Another possiblity for a right answer could have been:
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)


That's the same as the code I posted (just without the /text() optimization). Query plans below:
Query above:

Previous:




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Attachments
Forum.jpg (294 views, 68.00 KB)
Forum2.jpg (292 views, 69.00 KB)
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8826 Visits: 11734
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 that 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!


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6386 Visits: 7193
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...

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Ken Wymore
Ken Wymore
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4642 Visits: 2356
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.
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6386 Visits: 7193
KWymore (1/27/2012)
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.


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.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
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