Querying XML

Question

Querying XML

Steve Jones - SSC Editor

SSC Guru

Points: 736478
More actions
June 21, 2016 at 10:00 pm

#72587

Comments posted to this topic are about the item Querying XML
June 21, 2016 at 11:06 pm

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

I couldn't remember whether the node numbers were 0 based or 1 based. But then decided I might as well go for 1-based as the second Customer node didn't contain two CustomerName nodes and NULL wasn't an answer option.

Tom

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

If you query node 2 in the FROM clause, you still get values.

Acme1234 is returned.

TomThomson SSC Guru Points: 104773 More actions · Answer 3

Steve Jones - SSC Editor (6/22/2016)
If you query node 2 in the FROM clause, you still get values.
Acme1234 is returned.

yes, but not if you change the other [1] to [2] as well - ie the node whose value you want - because there's only one node at that level of each node at the level above.

Tom

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Querying XML is still painful for me.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Revenant SSC-Forever Points: 42467 More actions · Answer 5

I am really thankful that v13 handles JSON.

Thanks for the question, Steve!

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 6

TomThomson (6/22/2016)
Steve Jones - SSC Editor (6/22/2016)
If you query node 2 in the FROM clause, you still get values.
Acme1234 is returned.
yes, but not if you change the other [1] to [2] as well - ie the node whose value you want - because there's only one node at that level of each node at the level above.

Correct. But this doesn't impact whether XML is 0 or 1 based. Changing the FROM clause means there are two possible answers. 1 if this is 0 based, and 1 if this is 1 based.

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Steve Jones - SSC Editor (6/22/2016)
TomThomson (6/22/2016)
Steve Jones - SSC Editor (6/22/2016)
If you query node 2 in the FROM clause, you still get values.
Acme1234 is returned.
yes, but not if you change the other [1] to [2] as well - ie the node whose value you want - because there's only one node at that level of each node at the level above.
Correct. But this doesn't impact whether XML is 0 or 1 based. Changing the FROM clause means there are two possible answers. 1 if this is 0 based, and 1 if this is 1 based.

That reads as if we agree - but if we do, I've no idea why you made the first comment above (although it, being part of what we agree on, is of course correct).

Tom

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 8

I think I was confused as to what you were looking at. I see now since there aren't two CustomerName items in each element that there's a good chance that I've given something away. I was focused on the WHERE clause here. I should have altered the document to have two CustomerName elements in each node.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

I got this right "just" by deduction (I don't use or work with XML) but, my word, let's add to the list of reasons why I hate XML, shall we? 😛 As if the 10:1 bloat factor in this example isn't bad enough. :sick:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 10

Jeff Moden (6/25/2016)
I got this right "just" by deduction (I don't use or work with XML) but, my word, let's add to the list of reasons why I hate XML, shall we? 😛 As if the 10:1 bloat factor in this example isn't bad enough. :sick:

I do agree on the bloat factor but I see inefficient queries being even more of a problem. It seems like just being able to produce a result set is currently the acceptable standard, regardless of the (in)efficiency of the query. As and example, this code here is roughly 100 times more efficient than Steve's QoD code.

😎

DECLARE @xml XML;

SET @xml = N'

<Order>

<OrderID>RF4467</OrderID>

<ORderDate>2019-07-20-00.31.23.000000</ORderDate>

<Status>Open</Status>

<Customer>

<CustomerName Type="Individual">

<FirstName>Jon</FirstName>

<LastName>Doe</LastName>

</CustomerName>

</Customer>

<Customer Type = "Company">

<CustomerName>

<CompanyName>Acme</CompanyName>

<Account>12345</Account>

</CustomerName>

</Customer>

</Order>

';

SELECT @xml.value('(//CustomerName)[1]', 'NVARCHAR(100)') AS ORDERID

Looking at the execution plan then the difference between the two is quite obvious (Smilies injected by SSC)

ORIGINAL CODE

Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions

-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------

1 1 SELECT t.b.value('(CustomerName)[1]', 'NVARCHAR(100)') AS ORDERID FROM @xml.nodes('/Order/Customer[1]') t(b) 1 1 0 NULL NULL NULL NULL 66 NULL NULL NULL 201.6805 NULL NULL SELECT 0 NULL

0 0 |--Compute Scalar(DEFINE:([Expr1022]=[Expr1021])) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1022]=[Expr1021]) [Expr1022]=[Expr1021] 66 0 6.6E-06 111 201.6805 [Expr1022] NULL PLAN_ROW 0 1

1 1 |--Nested Loops(Inner Join, OUTER REFERENCES:(XML Reader with XPath filter.[id])) 1 3 2 Nested Loops Inner Join OUTER REFERENCES:(XML Reader with XPath filter.[id]) NULL 66 0 0.00027588 111 201.6805 [Expr1021] NULL PLAN_ROW 0 1

1 1 |--Nested Loops(Inner Join, OUTER REFERENCES:(XML Reader with XPath filter.[id])) 1 4 3 Nested Loops Inner Join OUTER REFERENCES:(XML Reader with XPath filter.[id]) NULL 66 0 0.00027588 461 67.02892 XML Reader with XPath filter.[id] NULL PLAN_ROW 0 1

1 1 | |--Filter(WHERE:(STARTUP EXPR([@xml] IS NOT NULL))) 1 5 4 Filter Filter WHERE:(STARTUP EXPR([@xml] IS NOT NULL)) NULL 66 0 5.6E-05 461 1.004056 XML Reader with XPath filter.[id] NULL PLAN_ROW 0 1

1 1 | | |--Table-valued function 1 6 5 Table-valued function Table-valued function NULL NULL 200 0 1.004 461 1.004 XML Reader with XPath filter.[id] NULL PLAN_ROW 0 1

1 1 | |--Top(TOP EXPRESSION:((1))) 1 12 4 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 461 66.02458 XML Reader with XPath filter.[id] NULL PLAN_ROW 0 66

0 0 | |--Compute Scalar(DEFINE:([Expr1007]=0x58)) 1 13 12 Compute Scalar Compute Scalar DEFINE:([Expr1007]=0x58) [Expr1007]=0x58 1 0 1E-07 913 66.02457 XML Reader with XPath filter.[id], [Expr1007] NULL PLAN_ROW 0 66

1 1 | |--Filter(WHERE:(XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1)))) 1 14 13 Filter Filter WHERE:(XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1))) NULL 1 0 1.224E-05 461 66.02457 XML Reader with XPath filter.[id] NULL PLAN_ROW 0 66

1 1 | |--Table-valued function 1 15 14 Table-valued function Table-valued function NULL NULL 18 0 1.00036 461 66.02377 XML Reader with XPath filter.[id] NULL PLAN_ROW 0 66

1 1 |--Stream Aggregate(DEFINE:([Expr1021]=MIN(CASE WHEN [@xml] IS NULL THEN NULL ELSE CASE WHEN datalength([Expr1018])>=(128) THEN CONVERT_IMPLICIT(nvarchar(100),[Expr1019],0) ELSE CONVERT_IMPLICIT(nvarchar(100),[Expr1018],0) END END))) 1 25 3 Stream Aggregate Aggregate NULL [Expr1021]=MIN(CASE WHEN [@xml] IS NULL THEN NULL ELSE CASE WHEN datalength([Expr1018])>=(128) THEN CONVERT_IMPLICIT(nvarchar(100),[Expr1019],0) ELSE CONVERT_IMPLICIT(nvarchar(100),[Expr1018],0) END END) 1 0 2.45E-05 111 134.6513 [Expr1021] NULL PLAN_ROW 0 66

1 1 |--UDX((XML Reader.[id], XML Reader.[nid], XML Reader.[tid], XML Reader.[value], XML Reader.[lvalue], XML Reader.[lvaluebin], [Expr1012], XML Reader with XPath filter.[id], XML Reader.[id])) 1 26 25 UDX UDX XQUERY DATAXML Reader.[id], XML Reader.[nid], XML Reader.[tid], XML Reader.[value], XML Reader.[lvalue], XML Reader.[lvaluebin], [Expr1012], XML Reader with XPath filter.[id], XML Reader.[id] [Expr1016][Expr1017][Expr1018][Expr1019][Expr1020] 40 0 4E-05 8045 134.6496 [Expr1018], [Expr1019] NULL PLAN_ROW 0 66

6 1 |--Nested Loops(Inner Join, OUTER REFERENCES:(XML Reader with XPath filter.[id], [Expr1025])) 1 27 26 Nested Loops Inner Join OUTER REFERENCES:(XML Reader with XPath filter.[id], [Expr1025]) NULL 50 0 0.006868441 13435 134.647 XML Reader with XPath filter.[id], [Expr1012], XML Reader.[id], XML Reader.[nid], XML Reader.[tid], XML Reader.[value], XML Reader.[lvalue], XML Reader.[lvaluebin] NULL PLAN_ROW 0 66

1 1 |--Top(TOP EXPRESSION:((1))) 1 28 27 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 1365 66.0247 XML Reader with XPath filter.[id], [Expr1012], [Expr1025] NULL PLAN_ROW 0 66

0 0 | |--Compute Scalar(DEFINE:([Expr1012]=0x58)) 1 29 28 Compute Scalar Compute Scalar DEFINE:([Expr1012]=0x58) [Expr1012]=0x58 1 0 1E-07 1365 66.0247 XML Reader with XPath filter.[id], [Expr1012], [Expr1025] NULL PLAN_ROW 0 66

1 1 | |--Filter(WHERE:(XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1)))) 1 30 29 Filter Filter WHERE:(XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1))) NULL 1 0 1.224E-05 913 66.02469 XML Reader with XPath filter.[id], [Expr1025] NULL PLAN_ROW 0 66

0 0 | |--Compute Scalar(DEFINE:([Expr1025]=getdescendantlimit(XML Reader with XPath filter.[id]))) 1 31 30 Compute Scalar Compute Scalar DEFINE:([Expr1025]=getdescendantlimit(XML Reader with XPath filter.[id])) [Expr1025]=getdescendantlimit(XML Reader with XPath filter.[id]) 18 0 1.8E-06 913 66.02388 XML Reader with XPath filter.[id], [Expr1025] NULL PLAN_ROW 0 66

1 1 | |--Table-valued function 1 32 31 Table-valued function Table-valued function NULL NULL 18 0 1.00036 461 66.02377 XML Reader with XPath filter.[id] NULL PLAN_ROW 0 66

6 1 |--Table-valued function 1 45 27 Table-valued function Table-valued function NULL NULL 1643.168 0 1.032863 12531 68.16898 XML Reader.[id], XML Reader.[nid], XML Reader.[tid], XML Reader.[value], XML Reader.[lvalue], XML Reader.[lvaluebin] NULL PLAN_ROW 0 66

(19 row(s) affected)

EE CODE

Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions

-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------

1 1 SELECT @xml.value('(//CustomerName)[1]', 'NVARCHAR(100)') AS ORDERID 3 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 1.044817 NULL NULL SELECT 0 NULL

0 0 |--Compute Scalar(DEFINE:([Expr1014]=[Expr1013])) 3 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1014]=[Expr1013]) [Expr1014]=[Expr1013] 1 0 1E-07 111 1.044817 [Expr1014] NULL PLAN_ROW 0 1

1 1 |--Stream Aggregate(DEFINE:([Expr1013]=MIN(CASE WHEN [@xml] IS NULL THEN NULL ELSE CASE WHEN datalength([Expr1010])>=(128) THEN CONVERT_IMPLICIT(nvarchar(100),[Expr1011],0) ELSE CONVERT_IMPLICIT(nvarchar(100),[Expr1010],0) END END))) 3 3 2 Stream Aggregate Aggregate NULL [Expr1013]=MIN(CASE WHEN [@xml] IS NULL THEN NULL ELSE CASE WHEN datalength([Expr1010])>=(128) THEN CONVERT_IMPLICIT(nvarchar(100),[Expr1011],0) ELSE CONVERT_IMPLICIT(nvarchar(100),[Expr1010],0) END END) 1 0 2.45E-05 111 1.044817 [Expr1013] NULL PLAN_ROW 0 1

1 1 |--UDX((XML Reader.[id], XML Reader.[nid], XML Reader.[tid], XML Reader.[value], XML Reader.[lvalue], XML Reader.[lvaluebin], [Expr1004], XML Reader with XPath filter.[id], XML Reader.[id])) 3 4 3 UDX UDX XQUERY DATAXML Reader.[id], XML Reader.[nid], XML Reader.[tid], XML Reader.[value], XML Reader.[lvalue], XML Reader.[lvaluebin], [Expr1004], XML Reader with XPath filter.[id], XML Reader.[id] [Expr1008][Expr1009][Expr1010][Expr1011][Expr1012] 40 0 4E-05 8045 1.044792 [Expr1010], [Expr1011] NULL PLAN_ROW 0 1

6 1 |--Nested Loops(Inner Join, OUTER REFERENCES:(XML Reader with XPath filter.[id], [Expr1015])) 3 5 4 Nested Loops Inner Join OUTER REFERENCES:(XML Reader with XPath filter.[id], [Expr1015]) NULL 50 0 0.006868441 13435 1.044752 XML Reader with XPath filter.[id], [Expr1004], XML Reader.[id], XML Reader.[nid], XML Reader.[tid], XML Reader.[value], XML Reader.[lvalue], XML Reader.[lvaluebin] NULL PLAN_ROW 0 1

1 1 |--Top(TOP EXPRESSION:((1))) 3 6 5 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 1365 0.005020357 XML Reader with XPath filter.[id], [Expr1004], [Expr1015] NULL PLAN_ROW 0 1

0 0 | |--Compute Scalar(DEFINE:([Expr1004]=0x58, [Expr1015]=getdescendantlimit(XML Reader with XPath filter.[id]))) 3 7 6 Compute Scalar Compute Scalar DEFINE:([Expr1004]=0x58, [Expr1015]=getdescendantlimit(XML Reader with XPath filter.[id])) [Expr1004]=0x58, [Expr1015]=getdescendantlimit(XML Reader with XPath filter.[id]) 1 0 2E-05 1365 0.005020257 XML Reader with XPath filter.[id], [Expr1004], [Expr1015] NULL PLAN_ROW 0 1

1 1 | |--Table-valued function 3 8 7 Table-valued function Table-valued function NULL NULL 1 0 1.004 461 0.005020157 XML Reader with XPath filter.[id] NULL PLAN_ROW 0 1

6 1 |--Table-valued function 3 19 5 Table-valued function Table-valued function NULL NULL 1643.168 0 1.032863 12531 1.032863 XML Reader.[id], XML Reader.[nid], XML Reader.[tid], XML Reader.[value], XML Reader.[lvalue], XML Reader.[lvaluebin] NULL PLAN_ROW 0 1

(9 row(s) affected)

Edit: Added the STATISTICS PROFILE outputs.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

Eirikur Eiriksson (6/26/2016)
It seems like just being able to produce a result set is currently the acceptable standard, regardless of the (in)efficiency of the query.

With or without XML, that's been the "acceptable standard" for a lot of people since the first use of ~~SQL~~ code in general. :laugh:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 12

Jeff Moden (6/27/2016)
Eirikur Eiriksson (6/26/2016)
It seems like just being able to produce a result set is currently the acceptable standard, regardless of the (in)efficiency of the query.
With or without XML, that's been the "acceptable standard" for a lot of people since the first use of ~~SQL~~ code in general. :laugh:

touché

😎