文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >选择所有XML节点并生成Server表

问选择所有XML节点并生成Server表
EN

Stack Overflow用户
提问于 2022-01-21 02:50:30
回答 1查看 98关注 0票数 0

我被困在SQL -有人能帮忙吗？

下面显示了一个XML；我希望读取XML标记并在Server中创建一个表。

我尝试了如下所示的查询，但没有获取数据。结果集为空。我使用了交叉应用并将其转换为xml。

显然，我可以编写XPath查询来选择标记到列中，这正是我想要的，除了有1000个可能的标记名，而且我不想把它们全部写出来(而且可能遗漏一个)，因此我正在寻找解决方案，我们读取xml标记并生成带有值和标签的表。

代码语言：javascript
复制
SELECT 
    t.ID,
    T0.Color.value('local-name(.)', 'nvarchar(max)') AS TagName,
    T0.Color.value('(@name)[1]','nvarchar(max)') AS EntityName,
    T0.Color.value('text()[1]','nvarchar(max)') AS TagValue  
FROM
    ConfigurationDnaItem t
CROSS APPLY 
    (SELECT 
         CAST(t.Value AS XML)) AS A(AllNodes)
CROSS APPLY 
    A.AllNodes.nodes('/hierarchy/nodes/list-entity') AS T0(Color)
WHERE 
    id = '1D09BFCB-AE0A-4346-835D-4BBCF2BBB503'

所需输出

请看上面的表格结构，我正在寻找从选择N/A中移除最后一行会好得多。

谢谢你帮忙。

代码语言：javascript
复制
<?xml version="1.0" encoding="utf-16"?>
<hierarchy name="DGPaintH" xmlns="urn:guru.cincom.com-Hierarchy">
    <nodes>
        <list-entity name="HubPaintDetail">
          <values>
            <value>
              <key-value><![CDATA[AFL.9H1B]]></key-value>
              <characteristics>
                <characteristic name="Value" type="list"><![CDATA[AFL.9H1B]]></characteristic>
                <characteristic name="Description" type="string"><![CDATA[Fleet Red(PPG Code: AFL.9H1B)]]></characteristic>
                <characteristic name="JDE_ITEM_NUMBER" type="string"><![CDATA[]]></characteristic>
                <characteristic name="PRICE_EX_FACTORY" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_RETAIL" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="OPTION_WEIGHT" type="numeric">0.00</characteristic>
                <characteristic name="WEIGHT_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="QUANTITY" type="numeric">0</characteristic>
                <characteristic name="Name" type="string"><![CDATA[]]></characteristic>
                <characteristic name="CatCode1" type="string"><![CDATA[02]]></characteristic>
                <characteristic name="CatCode2" type="string"><![CDATA[33]]></characteristic>
                <characteristic name="CatCode3" type="string"><![CDATA[DP]]></characteristic>
              </characteristics>
            </value>
          </values>
          <extended-properties>
            <extended-property name="ALTNAME" type="string"><![CDATA[Hubs]]></extended-property>
            <extended-property name="CatCode1" type="string"><![CDATA[02]]></extended-property>
            <extended-property name="CatCode2" type="string"><![CDATA[33]]></extended-property>
            <extended-property name="CatCode3" type="string"><![CDATA[DP]]></extended-property>
          </extended-properties>
        </list-entity>
        <list-entity name="DoorPaintDetail">
          <values>
            <value>
              <key-value><![CDATA[AFL.DTT6]]></key-value>
              <characteristics>
                <characteristic name="Value" type="list"><![CDATA[AFL.DTT6]]></characteristic>
                <characteristic name="Description" type="string"><![CDATA[GELCOAT WHITE]]></characteristic>
                <characteristic name="JDE_ITEM_NUMBER" type="string"><![CDATA[]]></characteristic>
                <characteristic name="PRICE_EX_FACTORY" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_RETAIL" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="OPTION_WEIGHT" type="numeric">0.00</characteristic>
                <characteristic name="WEIGHT_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="QUANTITY" type="numeric">0</characteristic>
                <characteristic name="Name" type="string"><![CDATA[]]></characteristic>
                <characteristic name="CatCode1" type="string"><![CDATA[02]]></characteristic>
                <characteristic name="CatCode2" type="string"><![CDATA[33]]></characteristic>
                <characteristic name="CatCode3" type="string"><![CDATA[DS]]></characteristic>
              </characteristics>
            </value>
          </values>
          <extended-properties>
            <extended-property name="ALTNAME" type="string"><![CDATA[Door]]></extended-property>
            <extended-property name="CatCode1" type="string"><![CDATA[02]]></extended-property>
            <extended-property name="CatCode2" type="string"><![CDATA[33]]></extended-property>
            <extended-property name="CatCode3" type="string"><![CDATA[DS]]></extended-property>
          </extended-properties>
        </list-entity>
        <list-entity name="UnderPaintDetail">
          <values>
            <value>
              <key-value><![CDATA[AFL.9H1B]]></key-value>
              <characteristics>
                <characteristic name="Value" type="list"><![CDATA[AFL.9H1B]]></characteristic>
                <characteristic name="Description" type="string"><![CDATA[Fleet Red(PPG Code: AFL.9H1B)]]></characteristic>
                <characteristic name="JDE_ITEM_NUMBER" type="string"><![CDATA[]]></characteristic>
                <characteristic name="PRICE_EX_FACTORY" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_RETAIL" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="OPTION_WEIGHT" type="numeric">0.00</characteristic>
                <characteristic name="WEIGHT_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="QUANTITY" type="numeric">0</characteristic>
                <characteristic name="Name" type="string"><![CDATA[]]></characteristic>
                <characteristic name="CatCode1" type="string"><![CDATA[02]]></characteristic>
                <characteristic name="CatCode2" type="string"><![CDATA[33]]></characteristic>
                <characteristic name="CatCode3" type="string"><![CDATA[DM]]></characteristic>
              </characteristics>
            </value>
          </values>
          <extended-properties>
            <extended-property name="ALTNAME" type="string"><![CDATA[Under]]></extended-property>
            <extended-property name="CatCode1" type="string"><![CDATA[02]]></extended-property>
            <extended-property name="CatCode2" type="string"><![CDATA[33]]></extended-property>
            <extended-property name="CatCode3" type="string"><![CDATA[DM]]></extended-property>
          </extended-properties>
        </list-entity>
        <list-entity name="PaintFleetMasterBoxesList">
          <values>
            <value>
              <key-value><![CDATA[AFL.9H1B]]></key-value>
              <characteristics>
                <characteristic name="Value" type="list"><![CDATA[AFL.9H1B]]></characteristic>
                <characteristic name="Description" type="string"><![CDATA[Fleet Red(PPG Code: AFL.9H1B)]]></characteristic>
                <characteristic name="JDE_ITEM_NUMBER" type="string"><![CDATA[]]></characteristic>
                <characteristic name="PRICE_EX_FACTORY" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_RETAIL" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="OPTION_WEIGHT" type="numeric">0.00</characteristic>
                <characteristic name="WEIGHT_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="QUANTITY" type="numeric">0</characteristic>
                <characteristic name="HasNSO" type="boolean">false</characteristic>
                <characteristic name="CatCode1" type="string"><![CDATA[02]]></characteristic>
                <characteristic name="CatCode2" type="string"><![CDATA[33]]></characteristic>
                <characteristic name="CatCode3" type="string"><![CDATA[DS]]></characteristic>
              </characteristics>
            </value>
          </values>
          <extended-properties>
            <extended-property name="ALTNAME" type="string"><![CDATA[Boxes]]></extended-property>
          </extended-properties>
        </list-entity>
        <list-entity name="RadiusGuardPaint">
          <values>
            <value>
              <key-value><![CDATA[N/A]]></key-value>
              <characteristics>
                <characteristic name="Value" type="list"><![CDATA[N/A]]></characteristic>
                <characteristic name="Description" type="string"><![CDATA[Guard Colour not applicable]]></characteristic>
                <characteristic name="JDE_ITEM_NUMBER" type="string"><![CDATA[]]></characteristic>
                <characteristic name="PRICE_EX_FACTORY" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_RETAIL" type="numeric">0.00</characteristic>
                <characteristic name="PRICE_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="OPTION_WEIGHT" type="numeric">0.00</characteristic>
                <characteristic name="WEIGHT_DIFFERENTIAL" type="numeric">0.00</characteristic>
                <characteristic name="QUANTITY" type="numeric">1</characteristic>
              </characteristics>
            </value>
          </values>
          <extended-properties>
            <extended-property name="ALTNAME" type="string"><![CDATA[Radius Guard Colour]]></extended-property>
            <extended-property name="CatCode1" type="string"><![CDATA[02]]></extended-property>
            <extended-property name="CatCode2" type="string"><![CDATA[33]]></extended-property>
            <extended-property name="CatCode3" type="string"><![CDATA[DS]]></extended-property>
          </extended-properties>
        </list-entity>
    </nodes>
</hierarchy>
xquery-sql
sql
sql-server
xml
tsql
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-01-21 03:33:26

目前还不清楚您的预期输出是什么，但是看起来您想要这样的东西

代码语言：javascript
复制
WITH XMLNAMESPACES(DEFAULT 'urn:guru.cincom.com-Hierarchy')
SELECT t.ID
      ,T0.Color.value('(extended-properties/extended-property[@name = "ALTNAME"]/text())[1]', 'nvarchar(max)') AS TagName
      ,T0.Color.value('@name','nvarchar(max)') AS EntityName
      ,T0.Color.value('(values/value/key-value/text())[1]','nvarchar(max)') AS TagValue  
FROM ConfigurationDnaItem t
CROSS APPLY (SELECT CAST(t.Value AS XML)) As A(AllNodes)
CROSS APPLY A.AllNodes.nodes('/hierarchy/nodes/list-entity[not(values/value/key-value/text() = "N/A")]') AS T0(Color)
where t.id = '1D09BFCB-AE0A-4346-835D-4BBCF2BBB503';

您的现有查询存在一些问题：

  • 您缺少名称空间声明。
  • local-name(.)会为您提供节点名list-entity，但还不清楚您为什么要这样做。
  • 您似乎想要的一些数据存储在extended-properties/extended-property中，需要通过属性name进行筛选，然后取出内部文本。
  • @name为您获取name属性，该属性不存在于您的图像中。不过，我把它忘在里面了。它不需要[1]。
  • text()[1]不会给您任何东西，因为上下文节点没有任何直接的内部文本节点。相反，你似乎想下降到values/value/key-value/text()。
  • 要筛选出N/A值，需要以下谓词[not(values/value/key-value/text() = "N/A")]

db<>fiddle

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/70795787

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档