Xpath query get value of node in a XML variable multinode

Question

Hi all.
I tried for get the sku node... but I have limited skills in xPath (xpath or Xquery? I'm confused about definition...).
Anyway... the first field I gotcha... but the second is always NULL.

I understood the base node.. but how can I go back to sku node?
I need a cross join?
FROM @XML .nodes('./enfinity/product/custom-attributes/custom-attribute/value') AS x(i)
CROSS JOIN @XML .nodes('./enfinity/product/sku') AS y(s)

Thanks ALEN, Italy

 DECLARE @xml xml  
    SET @xml = N'  
    <enfinity xmlns:dt="http://www.microsoft.com">  
      <product sku="2018031600">  
    		<sku>2018031600</sku>  
    		<long-description xml:lang="en-US">Asteral 3435</long-description>  
    	    <category-links>  
    		  <category-link name="CAT323" domain="Farmaton-PRODOTTI" default="1" hotdeal="0" />  
    		</category-links>  
    		<custom-attributes>  
    		  <custom-attribute name="GTINs" dt:dt="string">  
    			<value>7332543579785</value>  
    			<value>7332543598922</value>  
    		  </custom-attribute>  
    		  <custom-attribute name="PID_OWNER_SellerID@Gallenca-MasterRepository" dt:dt="string">Farmaton</custom-attribute>  
    		  <custom-attribute name="PID_VALUE_SellerID@Gallenca-MasterRepository" dt:dt="string">2018031600</custom-attribute>  
    		  <custom-attribute name="Weight" dt:dt="quantity" xml:lang="it-IT">0.05 kg</custom-attribute>  
    		</custom-attributes>  
    	</product>  
    </enfinity>'  
      
    SELECT   
    	x.i.value('(./text())[1]', 'VARCHAR(20)') as GTIN,  
    	x.i.value('(../../sku/text())[1]', 'VARCHAR(20)') as Sku  
    FROM @xml.nodes('./enfinity/product/custom-attributes/custom-attribute/value') AS x(i)  

Answer 1

There is a better way to handle the scenario. It is not a good idea to traverse up XPath axis.

SQL

DECLARE @xml xml = 
N'<enfinity xmlns:dt="http://www.microsoft.com">
<product sku="2018031600">
        <sku>2018031600</sku>
        <long-description xml:lang="en-US">Asteral 3435</long-description>
        <category-links>
        <category-link name="CAT323" domain="Farmaton-PRODOTTI" default="1" hotdeal="0" />
        </category-links>
        <custom-attributes>
        <custom-attribute name="GTINs" dt:dt="string">
            <value>7332543579785</value>
            <value>7332543598922</value>
        </custom-attribute>
        <custom-attribute name="PID_OWNER_SellerID@Gallenca-MasterRepository" dt:dt="string">Farmaton</custom-attribute>
        <custom-attribute name="PID_VALUE_SellerID@Gallenca-MasterRepository" dt:dt="string">2018031600</custom-attribute>
        <custom-attribute name="Weight" dt:dt="quantity" xml:lang="it-IT">0.05 kg</custom-attribute>
        </custom-attributes>
    </product>
</enfinity>';


;with xmlnamespaces ('http://www.microsoft.com' as dt)
SELECT 
    c.value('(sku/text())[1]', 'VARCHAR(20)') as sku,
    e.value('(./text())[1]', 'VARCHAR(20)') as GTIN,
    d.value('(@dt:dt)', 'VARCHAR(20)') as dt
FROM @xml.nodes('/enfinity/product') AS t(c)
    CROSS APPLY t.c.nodes('custom-attributes/custom-attribute[1]') as t2(d)
    CROSS APPLY t2.d.nodes('value') as t3(e);

Answer 2

Try these expressions:

. . .
x.i.value('(../../../sku/text())[1]', 'VARCHAR(20)') as Sku,
x.i.value('(../../../@sku)', 'VARCHAR(20)') as Sku
. . .

The former gets the <sku> element, the latter — the sku attribute of <product>.