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 Thursday, January 26, 2012 9:36 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 4,018, Visits: 5,315
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”
Post #1242579
Posted Thursday, January 26, 2012 9:58 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1242582
Posted Thursday, January 26, 2012 11:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 13,263, Visits: 11,053
Buh, got it wrong. But that's because I refuse to learn XQuery



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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1242596
Posted Friday, January 27, 2012 12:21 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 4,018, Visits: 5,315
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”
Post #1242635
Posted Friday, January 27, 2012 12:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:28 PM
Points: 13,263, Visits: 11,053
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1242637
Posted Friday, January 27, 2012 12:40 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
Forum.jpg (286 views, 68.58 KB)
Forum2.jpg (284 views, 69.98 KB)
Post #1242642
Posted Friday, January 27, 2012 1:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 6,043, Visits: 8,324
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
Post #1242658
Posted Friday, January 27, 2012 1:52 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 4,018, Visits: 5,315
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”
Post #1242659
Posted Friday, January 27, 2012 1:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:29 AM
Points: 3,354, Visits: 2,001
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.
Post #1242660
Posted Friday, January 27, 2012 2:15 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 4,018, Visits: 5,315
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”
Post #1242667
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse