Pivot complex XML using Xquery-CodePudding

Is it possible to pivot the following xml into the following result set, or get the structure as close to it as possible? It can obviously have more than 1 item with similar data, I have just trimmed it down so only item sku 987654 is in the file.

DECLARE @XML AS XML = '<data xsi:schemaLocation="http://www.intershop.com/xml/ns/enfinity/7.0/xcs/impex catalog.xsd http://www.intershop.com/xml/ns/enfinity/6.5/core/impex-dt dt.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.intershop.com/xml/ns/enfinity/7.0/xcs/impex" xmlns:xml="http://www.w3.org/XML/1998/namespace" xmlns:dt="http://www.intershop.com/xml/ns/enfinity/6.5/core/impex-dt" major="6" minor="1" family="enfinity" branch="enterprise" build="2.6.6-R-1.1.59.2-20210714.2">
<item sku="987654">
<sku>987654</sku>
<category-links>
<category-link name="abc" domain="WhiteStuff-DE-WebCategories" default = "0" hotdeal = "0"/>
<category-link name="def" domain="WhiteStuff-DE-WebCategories" default = "1" hotdeal = "0"/>
<category-link name="ghi" domain="WhiteStuff-DE-WebCategories" default = "0" hotdeal = "0"/>
</category-links>
<images>
<primary-view image-view="FF" />
<image-ref image-view="FD" image-type="w150" image-base-name="FD.jpg" domain="WhiteStuff" />
<image-ref image-view="FF" image-type="ORI" image-base-name="FF.jpg" domain="WhiteStuff" />
</images>
<variations>
<variation-attributes>
<variation-attribute name = "size">
<presentation-option>default</presentation-option>
<custom-attributes>
<custom-attribute name="displayName" dt:dt="string" xml:lang="en-US">Size</custom-attribute>
<custom-attribute name="productDetailUrl" xml:lang="de-DE" dt:dt="string">123.co.uk</custom-attribute>
</custom-attributes>
</variation-attribute>
<variation-attribute name = "colour">
<presentation-option>colorCode</presentation-option>
<presentation-product-attribute-name>rgbColour</presentation-product-attribute-name>
<custom-attributes>
<custom-attribute name="displayName" dt:dt="string" xml:lang="en-US">Colour</custom-attribute>
<custom-attribute name="productDetailUrl" xml:lang="de-DE" dt:dt="string">456.co.uk</custom-attribute>
</custom-attributes>
</variation-attribute>
</variation-attributes>
</variations>
</item>
</data>
'

This is my starting block:

;WITH XMLNAMESPACES 
(
    DEFAULT 'http://www.intershop.com/xml/ns/enfinity/7.0/xcs/impex',
    'http://www.intershop.com/xml/ns/enfinity/6.5/core/impex-dt' as dt
)

SELECT n.value('@sku', 'nvarchar(max)') as [sku]

    --[category-link],
    --[FD image],
    --[FF image],
    --[productDetailUrl DE],
    --[productDetailUrl EN]

FROM @XML.nodes('/data/item') as x(n);

CodePudding user response：

It is not so clear how to distinguish between languages:

[productDetailUrl DE]
[productDetailUrl EN]

Other than that, please try the following solution. It will get you started.

SQL

DECLARE @XML AS XML = 
N'<?xml version="1.0"?>
<data xsi:schemaLocation="http://www.intershop.com/xml/ns/enfinity/7.0/xcs/impex catalog.xsd http://www.intershop.com/xml/ns/enfinity/6.5/core/impex-dt dt.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns="http://www.intershop.com/xml/ns/enfinity/7.0/xcs/impex"
      xmlns:xml="http://www.w3.org/XML/1998/namespace"
      xmlns:dt="http://www.intershop.com/xml/ns/enfinity/6.5/core/impex-dt"
      major="6" minor="1" family="enfinity" branch="enterprise"
      build="2.6.6-R-1.1.59.2-20210714.2">
    <item sku="987654">
        <sku>987654</sku>
        <category-links>
            <category-link name="abc" domain="WhiteStuff-DE-WebCategories"
                           default="0" hotdeal="0"/>
            <category-link name="def" domain="WhiteStuff-DE-WebCategories"
                           default="1" hotdeal="0"/>
            <category-link name="ghi" domain="WhiteStuff-DE-WebCategories"
                           default="0" hotdeal="0"/>
        </category-links>
        <images>
            <primary-view image-view="FF"/>
            <image-ref image-view="FD" image-type="w150"
                       image-base-name="FD.jpg" domain="WhiteStuff"/>
            <image-ref image-view="FF" image-type="ORI" image-base-name="FF.jpg"
                       domain="WhiteStuff"/>
        </images>
        <variations>
            <variation-attributes>
                <variation-attribute name="size">
                    <presentation-option>default</presentation-option>
                    <custom-attributes>
                        <custom-attribute name="displayName" dt:dt="string"
                                          xml:lang="en-US">Size</custom-attribute>
                        <custom-attribute name="productDetailUrl"
                                          xml:lang="de-DE" dt:dt="string">123.co.uk</custom-attribute>
                    </custom-attributes>
                </variation-attribute>
                <variation-attribute name="colour">
                    <presentation-option>colorCode</presentation-option>
                    <presentation-product-attribute-name>rgbColour</presentation-product-attribute-name>
                    <custom-attributes>
                        <custom-attribute name="displayName" dt:dt="string"
                                          xml:lang="en-US">Colour</custom-attribute>
                        <custom-attribute name="productDetailUrl"
                                          xml:lang="de-DE" dt:dt="string">456.co.uk</custom-attribute>
                    </custom-attributes>
                </variation-attribute>
            </variation-attributes>
        </variations>
    </item>
</data>';

;WITH XMLNAMESPACES 
(
    DEFAULT 'http://www.intershop.com/xml/ns/enfinity/7.0/xcs/impex',
    'http://www.intershop.com/xml/ns/enfinity/6.5/core/impex-dt' as dt
)

SELECT c.value('@sku', 'nvarchar(max)') as [sku]
    , n.value('@name','VARCHAR(20)') AS [category-link]
    , c.value('(images/image-ref[@image-view="FD"]/@image-base-name)[1]','VARCHAR(20)') AS [FD image]
    , c.value('(images/image-ref[@image-view="FF"]/@image-base-name)[1]','VARCHAR(20)') AS [FF image]
    , c.value('(variations/variation-attributes/variation-attribute/custom-attributes/custom-attribute[@xml:lang="de-DE"]/text())[1]','VARCHAR(20)') AS [productDetailUrl DE]
    , c.value('(variations/variation-attributes/variation-attribute[@name="colour"]/custom-attributes/custom-attribute[@xml:lang="de-DE"]/text())[1]','VARCHAR(20)') AS [productDetailUrl EN]
FROM @XML.nodes('/data/item') as t(c)
    CROSS APPLY t.c.nodes('category-links/category-link') AS t2(n);

Output

 -------- --------------- ---------- ---------- --------------------- --------------------- 
|  sku   | category-link | FD image | FF image | productDetailUrl DE | productDetailUrl EN |
 -------- --------------- ---------- ---------- --------------------- --------------------- 
| 987654 | abc           | FD.jpg   | FF.jpg   | 123.co.uk           | 456.co.uk           |
| 987654 | def           | FD.jpg   | FF.jpg   | 123.co.uk           | 456.co.uk           |
| 987654 | ghi           | FD.jpg   | FF.jpg   | 123.co.uk           | 456.co.uk           |
 -------- --------------- ---------- ---------- --------------------- ---------------------