Pivot complex XML using Xquery

Question

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);

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question, no images. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Sep 13, 2021 at 18:08
@YitzhakKhabinsky sorry I thought it was self-explanatory, if you copy all the code into SSMS and run it then it'll execute successfully, but just missing rows and columns. What I'm looking for is 3 rows because there are 3 category links for one sku, then the rest are just columns but the row numbers don't change because of the pivot. So for example the in the xml there is an image-base-name FD.jpg where image-view is FD, that would go onto 1 column instead of additional row etc — tommyhmt
– tommyhmt, Commented Sep 13, 2021 at 18:35

miriamka · Accepted Answer · 2021-09-13 18:34:06Z

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           |
+--------+---------------+----------+----------+---------------------+---------------------+

That's worked like a charm thanks again, although there is one more requirement which I did not previously realise. Rather than having 2 columns FD image and FF image which are based on @image-view="??", is it possible to have just one column instead, where ?? is the image-view of primary-view, so in this case "FF", which would therefore retun "FF.jpg"?
I managed to add a new column: m.value('@image-view','VARCHAR(MAX)') AS [image-view] so that later on I could do: CASE WHEN [image-view] = 'FD' THEN [FD image] WHEN [image-view] = 'FF' THEN [FF image] END AS [image] But the problem is I won't know all possible values and so don't want any hardcoded lines, is there a way within @XML.nodes where I could embed m.value('@image-view','VARCHAR(MAX)') into c.value('(images/image-ref[@image-view="??"]/@image-base-name)[1]','VARCHAR(MAX)') somehow?

Collectives™ on Stack Overflow

Pivot complex XML using Xquery

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related