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)