Post reply

Querying XML

  • 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

  • June 22, 2016 at 5:27 am

    #1886188

    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

  • June 22, 2016 at 9:02 am

    #1886299

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

    Acme1234 is returned.

  • June 22, 2016 at 10:54 am

    #1886339

    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

  • June 22, 2016 at 11:19 am

    #1886345

    Querying XML is still painful for me.

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

  • June 22, 2016 at 1:33 pm

    #1886378

    I am really thankful that v13 handles JSON.

    Thanks for the question, Steve!

  • June 22, 2016 at 4:10 pm

    #1886402

    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.

  • June 24, 2016 at 1:21 pm

    #1886834

    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

  • June 25, 2016 at 10:44 am

    #1886898

    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.

  • June 25, 2016 at 2:56 pm

    #1886922

    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)

  • June 26, 2016 at 2:30 am

    #1886928

    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.

  • June 27, 2016 at 6:42 am

    #1887027

    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)

  • June 27, 2016 at 7:06 am

    #1887035

    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é

    😎

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

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