sqlsql-serverxmlopenxmldrilldown

Openxmls in SQL Server - drilling down in WITH clause


I have an XML like

<root sxfversion="1.6" gendate="2020-01-23 16:16:40">
<products>
    <p>
      <id>8244</id>
      <name>
        NAME OF ARTICLE
      </name>
      <reference>622801</reference>
      <ean13>3232870162282</ean13>
      <stock>17</stock>
      <price>
        <tax>23</tax>
        <netto>9.2</netto>
        <brutto>11.32</brutto>
      </price>
     </p>
   </products>
</root> 

I try make a query, for example:

SELECT 
    *
FROM
    OPENXML(@int, '/root/products/p', 2)
        WITH 
            (id int,
             name varchar(40),
             ean13 varchar(20)
            )

and it works.

enter image description here

Now I want use another (nested) column - price (tax, netto, brutto).

I can do it with modifying of query like this (drilling up) and it also works:

SELECT 
    *
FROM
OPENXML(@int, '/root/products/p/price', 2)
WITH
(   
    id int '../id',
    name varchar(40) '../name',
    ean13 varchar(20) '../ean13',
    netto varchar(10),
    brutto varchar(10),
    tax varchar(10)
)

BUT why something like this (drilling down doesn't work properly? It duplicate value of price each row to all rows.

Is possible to drill down?

SELECT *
FROM OPENXML(@int, '/root/products/p', 2)
WITH ( id varchar(10), 
name varchar(40), 
ean13 varchar(20),
netto varchar(10) '../p/price/netto', 
brutto varchar(10) '//price/brutto', 
tax varchar(10) '//price/tax')

enter image description here


Solution

  • Is possible to drill down?

    Yes. Just don't drill up first. .. drills up one level and / starts at the root. To navigate relative to the current node use ..

    EG

    declare @doc nvarchar(max) = '
    <root sxfversion="1.6" gendate="2020-01-23 16:16:40">
    <products>
        <p>
          <id>8244</id>
          <name>
                            NAME OF ARTICLE
                        </name>
          <reference>622801</reference>
          <ean13>3232870162282</ean13>
          <stock>17</stock>
          <price>
            <tax>23</tax>
            <netto>9.2</netto>
            <brutto>11.32</brutto>
          </price>
        </p>
            <p>
          <id>8245</id>
          <name>
                            NAME OF ARTICLE2
                        </name>
          <reference>622801</reference>
          <ean13>3232870162282</ean13>
          <stock>17</stock>
          <price>
            <tax>43</tax>
            <netto>1.2</netto>
            <brutto>31.32</brutto>
          </price>
        </p>
    </products>
    </root>'
    
    declare @hDoc int
    EXEC sp_xml_preparedocument @hDoc OUTPUT, @doc;  
    
    SELECT *
    FROM OPENXML(@hDoc, '/root/products/p', 2)
    WITH ( 
        id varchar(10), 
        name varchar(40), 
        ean13 varchar(20),
        netto varchar(10) './price/netto', 
        brutto varchar(10) './price/brutto', 
        tax varchar(10) './price/tax'
    )
    
    EXEC sp_xml_removedocument @hDoc;  
    

    outputs

    id         name                                     ean13                netto      brutto     tax
    ---------- ---------------------------------------- -------------------- ---------- ---------- ----------
    8244       NAME OF ARTICLE                          3232870162282        9.2        11.32      23
    8245       NAME OF ARTICLE2                         3232870162282        1.2        31.32      43
    
    (2 rows affected)