Querying XML

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715446

    Comments posted to this topic are about the item Querying XML

  • This was removed by the editor as SPAM

  • TomThomson

    SSC Guru

    Points: 104767

    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: 715446

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

    Acme1234 is returned.

  • TomThomson

    SSC Guru

    Points: 104767

    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: 286958

  • Revenant

    SSC-Forever

    Points: 42467

    I am really thankful that v13 handles JSON.

    Thanks for the question, Steve!

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715446

    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: 104767

    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: 715446

    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: 994560

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182347

    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: 994560

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182347

    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 14 (of 14 total)

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