编程基础网 / 编程问答 / 数据库问题 / 正文

使用 Xquery 透视复杂的 XML

数据库问题
2021-01-01

Pivot complex XML using Xquery(使用 Xquery 透视复杂的 XML)

本文介绍了使用 Xquery 透视复杂的 XML的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

是否可以将以下 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！

本文标题为：使用 Xquery 透视复杂的 XML

上一篇： FOR XML 通过树概念中的属性进行多重控制
下一篇： 带有 for 循环的 xquery 中的 SQL Server 性能

基础教程推荐

学习HTML
学习jQuery
学习Laravel
学习CSS3
学习Vue.js
学习Bootstrap5
学习ThinkPHP
学习AJAX
猜你喜欢
最新文章
热门文章
编程基础
热门标签
网站首页 HTML/CSS 菜单 Layui Yii2