国产欧美日韩一区,成人在线日韩,成人免费网址

本文介紹了使用 Xquery 透視復雜的 XML的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！

問題描述

限時送ChatGPT賬號..

是否可以將以下 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           |
+--------+---------------+----------+----------+---------------------+---------------------+

這篇關于使用 Xquery 透視復雜的 XML的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！

pbootcms网站模板|日韩1区2区|织梦模板||网站源码|日韩1区2区|jquery建站特效-html5模板网

使用 Xquery 透視復雜的 XML

問題描述

推薦答案

相關文檔推薦