使用 Xquery 透视复杂的 XML

2021-10-02 00:00:00 xml tsql xquery sql-server

是否可以将以下 xml 转换为以下结果集，或让结构尽可能接近它?它显然可以有超过 1 个具有相似数据的项目，我刚刚对其进行了修剪，因此文件中只有项目 sku 987654.

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

这是我的起点:

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

推荐答案

不太清楚如何区分语言:

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

输出

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

相关文章