本文介紹了使用 Xquery 透視復(fù)雜的 XML的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)吧！
問題描述
限時送ChatGPT賬號..
是否可以將以下 xml 轉(zhuǎn)換為以下結(jié)果集，或讓結(jié)構(gòu)盡可能接近它?它顯然可以有超過 1 個具有相似數(shù)據(jù)的項目，我剛剛對其進(jìn)行了修剪，因此文件中只有項目 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);
推薦答案
不太清楚如何區(qū)分語言:
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 |
+--------+---------------+----------+----------+---------------------+---------------------+
這篇關(guān)于使用 Xquery 透視復(fù)雜的 XML的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！
【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內(nèi)容侵犯了您的權(quán)益，請聯(lián)系我們刪除處理，感謝您的支持！