如何使用XQuery对Intershop格式XML执行行转列获取目标结果集

需求说明 #

将给定的Intershop结构XML行转列输出目标结果集，或输出结构尽可能接近的数据集。实际场景中可存在多个同结构item条目，示例仅裁剪保留了sku为987654的条目。

示例XML定义 #

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

完整实现查询 #

通过XPath属性谓词过滤目标节点，多值节点采用聚合方式拼接输出，兼容所有支持XML查询的SQL Server版本：

;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,
    'http://www.w3.org/XML/1998/namespace' as xml
)
SELECT 
    n.value('@sku', 'nvarchar(50)') as [sku],
    -- 所有分类链接逗号拼接
    STUFF((SELECT ',' + cl.value('@name', 'nvarchar(50)') 
           FROM x.n.nodes('category-links/category-link') t(cl)
           FOR XML PATH('')),1,1,'') AS [category-link],
    -- 默认分类
    n.value('(category-links/category-link[@default="1"]/@name)[1]', 'nvarchar(50)') as [default-category],
    -- FD类型图片文件名
    n.value('(images/image-ref[@image-view="FD"]/@image-base-name)[1]', 'nvarchar(100)') as [FD image],
    -- FF类型图片文件名
    n.value('(images/image-ref[@image-view="FF"]/@image-base-name)[1]', 'nvarchar(100)') as [FF image],
    -- 德语站点详情页URL聚合
    STUFF((SELECT ',' + ca.value('text()[1]', 'nvarchar(200)')
           FROM x.n.nodes('variations/variation-attributes/variation-attribute/custom-attributes/custom-attribute[@name="productDetailUrl" and @xml:lang="de-DE"]') t(ca)
           FOR XML PATH('')),1,1,'') AS [productDetailUrl DE],
    -- 英语站点详情页URL聚合
    STUFF((SELECT ',' + ca.value('text()[1]', 'nvarchar(200)')
           FROM x.n.nodes('variations/variation-attributes/variation-attribute/custom-attributes/custom-attribute[@name="productDetailUrl" and @xml:lang="en-US"]') t(ca)
           FOR XML PATH('')),1,1,'') AS [productDetailUrl EN]
FROM @XML.nodes('/data/item') as x(n);

说明 #

如果使用SQL Server 2017及以上版本，可以将STUFF + FOR XML的聚合写法替换为STRING_AGG，语法更简洁：

-- 分类链接聚合写法示例
(SELECT STRING_AGG(cl.value('@name', 'nvarchar(50)'), ',') FROM x.n.nodes('category-links/category-link') t(cl)) AS [category-link]

示例输出结果 #

内容的提问来源于stack exchange，提问作者tommyhmt