文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >如何通过SQL选择XML中的所有内部节点标记

问如何通过SQL选择XML中的所有内部节点标记
EN

Stack Overflow用户
提问于 2022-02-09 01:29:08
回答 1查看 186关注 0票数 0

所需输出- sql数据集中我想要的绿色标记数据

有人能帮我吗？

我希望选择xml中的所有节点，并在我的数据集中获取它的键值和值。

我试过了，但我只能得到特定的标签结果。

我的xml中有数字实体、列表实体和字符串实体，我希望从每个标记中获取值。

到目前为止，我只能获得一个实体值，但我不知道如何编写或条件或查询中的东西来获得所有xmls标记的结果。

这是我的xml和我编写的查询：

代码语言：javascript
复制
--Query
WITH XMLNAMESPACES(DEFAULT 'urn:guru.cincom.com-Hierarchy')
SELECT t.ID
    
      ,T0.Color.value('@name','nvarchar(max)') AS EntityName
      ,T0.Color.value('(key-value/text())[1]','nvarchar(max)') AS EntityValue  
FROM ConfigurationDnaItem t
CROSS APPLY (SELECT CAST(t.Value AS XML)) As A(AllNodes)
CROSS APPLY A.AllNodes.nodes('/hierarchy/nodes/numeric-entity[not(values/value/key-value[text() = "N/A" or text() = "NA" or text() = "Nil"])]') AS T0(Color)
where t.id = 'BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67';

Xml

在这里输入图像描述

代码语言：javascript
复制
<?xml version="1.0" encoding="utf-16"?>
<hierarchy name="DGTrailerDimensionH" xmlns="urn:guru.cincom.com-Hierarchy">
  <nodes>
    <numeric-entity name="UnitLength">
      <key-value>14500</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[14500]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Overall Length]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <list-entity name="UnitHeightList">
      <values>
        <value>
          <key-value><![CDATA[4300]]></key-value>
          <characteristics>
            <characteristic name="Value" type="list"><![CDATA[4300]]></characteristic>
            <characteristic name="Description" type="string"><![CDATA[4300]]></characteristic>
            <characteristic name="DGName" type="string"><![CDATA[Overall Height]]></characteristic>
          </characteristics>
        </value>
      </values>
      <extended-properties />
    </list-entity>
    <numeric-entity name="CoamingHeight">
      <key-value>25</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[25]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Coaming Height above main]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <list-entity name="TurntableHeightList">
      <values>
        <value>
          <key-value><![CDATA[1270]]></key-value>
          <characteristics>
            <characteristic name="Value" type="list"><![CDATA[1270]]></characteristic>
            <characteristic name="Description" type="string"><![CDATA[1270]]></characteristic>
            <characteristic name="DGName" type="string"><![CDATA[Skidplate Height]]></characteristic>
          </characteristics>
        </value>
      </values>
      <extended-properties />
    </list-entity>
    <numeric-entity name="ApertureHeight">
      <key-value>2725</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[2725]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Aperture Height]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <numeric-entity name="ApertureLength">
      <key-value>14200</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[14200]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Aperture Length]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <numeric-entity name="FifthWheelLocationNum">
      <key-value>0</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[0]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Turntable Location]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <numeric-entity name="SuspensionLocation">
      <key-value>9350</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[9350]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Suspension Location]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <numeric-entity name="SuspensionSpreadNum">
      <key-value>2700</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[2700]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Suspension spread]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <string-entity name="KingPinLocationStr">
      <key-value><![CDATA[1550]]></key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[1550]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[King Pin Position]]></characteristic>
      </characteristics>
      <extended-properties />
    </string-entity>
    <numeric-entity name="RearApertureHeight">
      <key-value>0</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[0]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Rear Aperture Height]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
    <numeric-entity name="DropDeckHeightNum">
      <key-value>0</key-value>
      <characteristics>
        <characteristic name="Value" type="list"><![CDATA[0]]></characteristic>
        <characteristic name="DGName" type="string"><![CDATA[Drop Deck Height]]></characteristic>
      </characteristics>
      <extended-properties />
    </numeric-entity>
  </nodes>
</hierarchy>


  [1]: Out required - Green mark i want in data set.
sql
sql-server
xml
xquery
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2022-02-09 02:15:36

请尝试以下解决方案。它遵循相同的最小可复制示例范例。按原样将其复制到SSMS，运行它，它就可以工作了。

虽然我不理解XPath表达式谓词：

代码语言：javascript
复制
[not(values/value/key-value[text() = ("N/A","NA","Nil")])]

请澄清你想过滤掉的东西。

SQL

代码语言：javascript
复制
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID UNIQUEIDENTIFIER PRIMARY KEY, [value] NVARCHAR(MAX));
INSERT INTO @tbl (ID, value) VALUES
('BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67'
, N'<?xml version="1.0" encoding="utf-16"?>
<hierarchy name="DGTrailerDimensionH" xmlns="urn:guru.cincom.com-Hierarchy">
    <nodes>
        <numeric-entity name="UnitLength">
            <key-value>14500</key-value>
            <characteristics>
                <characteristic name="Value" type="list"><![CDATA[14500]]></characteristic>
                <characteristic name="DGName" type="string"><![CDATA[Overall Length]]></characteristic>
            </characteristics>
            <extended-properties/>
        </numeric-entity>
        <list-entity name="UnitHeightList">
            <values>
                <value>
                    <key-value><![CDATA[4300]]></key-value>
                    <characteristics>
                        <characteristic name="Value" type="list"><![CDATA[4300]]></characteristic>
                        <characteristic name="Description" type="string"><![CDATA[4300]]></characteristic>
                        <characteristic name="DGName" type="string"><![CDATA[Overall Height]]></characteristic>
                    </characteristics>
                </value>
            </values>
            <extended-properties/>
        </list-entity>
        <numeric-entity name="CoamingHeight">
            <key-value>25</key-value>
            <characteristics>
                <characteristic name="Value" type="list"><![CDATA[25]]></characteristic>
                <characteristic name="DGName" type="string"><![CDATA[Coaming Height above main]]></characteristic>
            </characteristics>
            <extended-properties/>
        </numeric-entity>
        <list-entity name="TurntableHeightList">
            <values>
                <value>
                    <key-value><![CDATA[1270]]></key-value>
                    <characteristics>
                        <characteristic name="Value" type="list"><![CDATA[1270]]></characteristic>
                        <characteristic name="Description" type="string"><![CDATA[1270]]></characteristic>
                        <characteristic name="DGName" type="string"><![CDATA[Skidplate Height]]></characteristic>
                    </characteristics>
                </value>
            </values>
            <extended-properties/>
        </list-entity>
        <numeric-entity name="ApertureHeight">
            <key-value>2725</key-value>
            <characteristics>
                <characteristic name="Value" type="list"><![CDATA[2725]]></characteristic>
                <characteristic name="DGName" type="string"><![CDATA[Aperture Height]]></characteristic>
            </characteristics>
            <extended-properties/>
        </numeric-entity>
        <numeric-entity name="ApertureLength">
            <key-value>14200</key-value>
            <characteristics>
                <characteristic name="Value" type="list"><![CDATA[14200]]></characteristic>
                <characteristic name="DGName" type="string"><![CDATA[Aperture Length]]></characteristic>
            </characteristics>
            <extended-properties/>
        </numeric-entity>
        <numeric-entity name="FifthWheelLocationNum">
            <key-value>0</key-value>
            <characteristics>
                <characteristic name="Value" type="list"><![CDATA[0]]></characteristic>
                <characteristic name="DGName" type="string"><![CDATA[Turntable Location]]></characteristic>
            </characteristics>
            <extended-properties/>
        </numeric-entity>
        <numeric-entity name="SuspensionLocation">
            <key-value>9350</key-value>
            <characteristics>
                <characteristic name="Value" type="list"><![CDATA[9350]]></characteristic>
                <characteristic name="DGName" type="string"><![CDATA[Suspension Location]]></characteristic>
            </characteristics>
            <extended-properties/>
        </numeric-entity>
        <numeric-entity name="SuspensionSpreadNum">
            <key-value>2700</key-value>
            <characteristics>
                <characteristic name="Value" type="list"><![CDATA[2700]]></characteristic>
                <characteristic name="DGName" type="string"><![CDATA[Suspension spread]]></characteristic>
            </characteristics>
            <extended-properties/>
        </numeric-entity>
        <string-entity name="KingPinLocationStr">
            <key-value><![CDATA[1550]]></key-value>
            <characteristics>
                <characteristic name="Value" type="list"><![CDATA[1550]]></characteristic>
                <characteristic name="DGName" type="string"><![CDATA[King Pin Position]]></characteristic>
            </characteristics>
            <extended-properties/>
        </string-entity>
        <numeric-entity name="RearApertureHeight">
            <key-value>0</key-value>
            <characteristics>
                <characteristic name="Value" type="list"><![CDATA[0]]></characteristic>
                <characteristic name="DGName" type="string"><![CDATA[Rear Aperture Height]]></characteristic>
            </characteristics>
            <extended-properties/>
        </numeric-entity>
        <numeric-entity name="DropDeckHeightNum">
            <key-value>0</key-value>
            <characteristics>
                <characteristic name="Value" type="list"><![CDATA[0]]></characteristic>
                <characteristic name="DGName" type="string"><![CDATA[Drop Deck Height]]></characteristic>
            </characteristics>
            <extended-properties/>
        </numeric-entity>
    </nodes>
</hierarchy>');
-- DDL and sample data population, end

WITH XMLNAMESPACES(DEFAULT 'urn:guru.cincom.com-Hierarchy')
SELECT t.ID
    , p.value('@name','nvarchar(max)') AS EntityName
    , p.value('(.//key-value/text())[1]','nvarchar(max)') AS EntityValue
    , c.value('(characteristic[@name="Value"]/text())[1]','nvarchar(max)') AS char_Value
    , c.value('(characteristic[@name="DGName"]/text())[1]','nvarchar(max)') AS char_GName
    , c.value('(characteristic[@name="Description"]/text())[1]','nvarchar(max)') AS char_desc
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST(t.Value AS XML)) AS A(AllNodes)
CROSS APPLY A.AllNodes.nodes('/hierarchy/nodes/*[not(values/value/key-value[text() = ("N/A","NA","Nil")])]') AS t0(p)
CROSS APPLY t0.p.nodes('.//characteristics') AS t1(c)
WHERE t.id = 'BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67';

输出

代码语言：javascript
复制
+--------------------------------------+-----------------------+-------------+------------+---------------------------+-----------+
|                  ID                  |      EntityName       | EntityValue | char_Value |        char_GName         | char_desc |
+--------------------------------------+-----------------------+-------------+------------+---------------------------+-----------+
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | UnitLength            |       14500 |      14500 | Overall Length            | NULL      |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | UnitHeightList        |        4300 |       4300 | Overall Height            | 4300      |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | CoamingHeight         |          25 |         25 | Coaming Height above main | NULL      |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | TurntableHeightList   |        1270 |       1270 | Skidplate Height          | 1270      |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | ApertureHeight        |        2725 |       2725 | Aperture Height           | NULL      |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | ApertureLength        |       14200 |      14200 | Aperture Length           | NULL      |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | FifthWheelLocationNum |           0 |          0 | Turntable Location        | NULL      |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | SuspensionLocation    |        9350 |       9350 | Suspension Location       | NULL      |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | SuspensionSpreadNum   |        2700 |       2700 | Suspension spread         | NULL      |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | KingPinLocationStr    |        1550 |       1550 | King Pin Position         | NULL      |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | RearApertureHeight    |           0 |          0 | Rear Aperture Height      | NULL      |
| BA84B6C7-B6F7-4FC9-9DD8-8C9022513A67 | DropDeckHeightNum     |           0 |          0 | Drop Deck Height          | NULL      |
+--------------------------------------+-----------------------+-------------+------------+---------------------------+-----------+
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/71042953

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

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

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

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

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