XML Parse Query
-
Need help with the following query:
DECLARE @XML XML;
SET @XML = '<xfadata>
<changeDebitCard>
<applicantInfo>
<txtApplicantName>FirstName LastName</txtApplicantName>
</applicantInfo>
<posAccts>
<txtAcctNo>99999999</txtAcctNo>
<OptInFlag>1</OptInFlag>
</posAccts>
</changeDebitCard>
</xfadata>'
SELECT DV.ApplicantName
FROM @XML.nodes('./xfadata/changeDebitCard/applicantInfo')
AS T(customers)
CROSS
APPLY (
SELECT customers.value('(txtApplicantName/text())[1]', 'VARCHAR(100)')
)
AS DV (ApplicantName);
RESULT:
ApplicantName
FirstName LastName
How can this query be rewritten to pull in the other xml fields so that the query results will look like this:
ApplicantName AcctNo OptIn
FirstName Lastname 99999999 1
Please help.
Thx
-
Something like the following?
SELECTU.v.value('txtApplicantName[1]', 'VARCHAR(30)') as a,
W.x.value('txtAcctNo[1]', 'VARCHAR(30)') as b,
W.x.value('OptInFlag[1]', 'VARCHAR(30)') as c
FROM @XML.nodes('xfadata/changeDebitCard') T(c)
CROSS APPLY
c.nodes('applicantInfo') U(v)
CROSS APPLY
c.nodes('posAccts') W(x)
-
Thank you so much!
-
This works too:
SELECT n1.value('.', 'VARCHAR(30)'),
n2.value('.', 'INTEGER'),
n3.value('.', 'BIT')
FROM @XML.nodes('./xfadata/changeDebitCard') Nodes (node)
CROSS
APPLY (
SELECT n1 = node.query('./applicantInfo/txtApplicantName'),
n2 = node.query('./posAccts/txtAcctNo'),
n3 = node.query('./posAccts/OptInFlag')
) CA;
Anyone interest in a FLWOR version?
🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Paul White (2/27/2010)
...Anyone interest in a FLWOR version?
🙂
Sure I am! It's "skills improvement" Saturday anyway! ;-):-D
-
SELECT name = node.value('./@name', 'VARCHAR(30)'),
account = node.value('./@account', 'INTEGER'),
flag = node.value('./@flag', 'BIT')
FROM (
SELECT @XML.query
(
'
for $CDC in ./xfadata/changeDebitCard
return
<Record
name="{$CDC/applicantInfo/txtApplicantName}"
account="{$CDC/posAccts/txtAcctNo}"
flag="{$CDC/posAccts/OptInFlag}">
</Record>
')
) T (n)
CROSS
APPLY n.nodes('./Record') U (node);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
@paul-2: Interesting approach!
Which one out of the three solutions would you actually use on the given scenario (extended volume)?
I'm not sure if the execution plan shows real figures on that scenario. But if it does, the last solution doesn't really look like an option...
-
lmu92 (2/27/2010)
@Paul: Interesting approach!Which one out of the three solutions would you actually use on the given scenario (extended volume)?
I'm not sure if the execution plan shows real figures on that scenario. But if it does, the last solution doesn't really look like an option...
Thanks. The last one is just a bit of fun really 🙂
In the real world, I'd probably use your one, though there's not much to choose between the first two, so it's possibly just a question of style.
All of the approaches could benefit from typed XML of course, and if the data were from a table, XML indexes would help too.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Paul White (2/27/2010)
...In the real world, I'd probably use your one, though there's not much to choose between the first two, so it's possibly just a question of style.
...
Paul
I compared your solution and mine using exec plan and mine is about 10-15 times faster (based on that limited sample data). Haven't compared it with a "real world" scenario since I don't have sample data available. So I was hoping you had some previous test results available. If so, do those two solutions scale different when used against xml variable, xml column without index and xml column with index? If you don't have any test results I probably will give it a try and see what happens....
Regarding the last solution: I would consider it being the "cursor for xml data" approach. It works, but there are much more efficient ways. 😀
-
lmu92 (2/28/2010)
Paul White (2/27/2010)
I compared your solution and mine using exec plan and mine is about 10-15 times faster (based on that limited sample data).Really? I did run both last night, and both took zero milliseconds :doze:
Are you comparing estimated costs?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Paul White (2/28/2010)
lmu92 (2/28/2010)
...Really? I did run both last night, and both took zero milliseconds :doze:
Are you comparing estimated costs?
You're right in terms of execution time. But when looking at the execution plan it seems a lot different.
Herer's what I tried:
DECLARE @XML XML;SET @XML = '<xfadata>
<changeDebitCard>
<applicantInfo>
<txtApplicantName>FirstName LastName</txtApplicantName>
</applicantInfo>
<posAccts>
<txtAcctNo>99999999</txtAcctNo>
<OptInFlag>1</OptInFlag>
</posAccts>
</changeDebitCard>
</xfadata>'
SELECT
U.v.value('txtApplicantName[1]', 'VARCHAR(30)') as a,
W.x.value('txtAcctNo[1]', 'VARCHAR(30)') as b,
W.x.value('OptInFlag[1]', 'VARCHAR(30)') as c
FROM @XML.nodes('xfadata/changeDebitCard') T(c)
CROSS APPLY
c.nodes('applicantInfo') U(v)
CROSS APPLY
c.nodes('posAccts') W(x)
SELECT n1.value('.', 'VARCHAR(30)'),
n2.value('.', 'INTEGER'),
n3.value('.', 'BIT')
FROM @XML.nodes('./xfadata/changeDebitCard') Nodes (node)
CROSS
APPLY (
SELECT n1 = node.query('./applicantInfo/txtApplicantName'),
n2 = node.query('./posAccts/txtAcctNo'),
n3 = node.query('./posAccts/OptInFlag')
) CA;
And here's a picture of the actual execution plan I got:
-
Paul White (2/27/2010)
SELECT name = node.value('./@name', 'VARCHAR(30)'),
account = node.value('./@account', 'INTEGER'),
flag = node.value('./@flag', 'BIT')
FROM (
SELECT @XML.query
(
'
for $CDC in ./xfadata/changeDebitCard
return
<Record
name="{$CDC/applicantInfo/txtApplicantName}"
account="{$CDC/posAccts/txtAcctNo}"
flag="{$CDC/posAccts/OptInFlag}">
</Record>
')
) T (n)
CROSS
APPLY n.nodes('./Record') U (node);
Paul:
Any chance you could give us a breakdown of how this works? I've been trying to learn FLWOR for a while now, and this seems like a good example, learning-wise. I'd be especially interested in why FLWOR alone was not enough and the CROSS APPLY was still needed.
Thanks! 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Barry,
Just a quick reply because it is late.
To answer your second question: FLWOR is specified as an XQuery within a query method, so it returns an instance of untyped XML. The APPLY to the nodes method is necessary to shred it for the value methods, since the FLWOR could return multiple <Record> elements.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Paul White (2/28/2010)
lmu92 (2/28/2010)
Paul White (2/27/2010)
I compared your solution and mine using exec plan and mine is about 10-15 times faster (based on that limited sample data).Really? I did run both last night, and both took zero milliseconds :doze:
Are you comparing estimated costs?
The data's probably too small to give differences in elapsed times.
What I did notice though is that adding the text function ("(.../text())[1]") really seems to help the optimizer to produce a much more efficient XML query (I actually learned this from you, Paul :-)). Just compare the query plan's for Lutz's with & without:
SELECT
U.v.value('(txtApplicantName/text())[1]', 'VARCHAR(30)') as a,
W.x.value('(txtAcctNo/text())[1]', 'VARCHAR(30)') as b,
W.x.value('(OptInFlag/text())[1]', 'VARCHAR(30)') as c
FROM @XML.nodes('xfadata/changeDebitCard') T(c)
CROSS APPLY c.nodes('applicantInfo') U(v)
CROSS APPLY c.nodes('posAccts') W(x)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply