时间戳

首页 > 解决方案 > Q. 如何从 XSD 字符串字段中选择命名元素值

sql - Q. 如何从 XSD 字符串字段中选择命名元素值

问题描述

在 SQL Server 中，我试图从我认为是 XSD 字符串的值中选择值。

例如，给定以下 SQL Server 表

CREATE TABLE dbo.TextXML (
ID INT PRIMARY KEY,
Data NVARCHAR(max))

INSERT INTO dbo.TextXML ( ID, Data )
SELECT 1, 
       ('<element name="Rownum" value="Row23" />
          <element name="CONNECTIONTYPESECTION" value="True" />
          <element name="CustomFields" />
          <element name="EchoData">
            <element name="0000" value="8220000000000000" />
            <element name="0001" value="0400000100000000" />
            <element name="0007" value="0805193143" />
            <element name="MessageType" value="1234" />
          </element>
          <element name="ENABLESSL" value="false" />' )

INSERT INTO dbo.TextXML ( ID, Data )
SELECT 2, 
    ('<element name="Rownum" value="Row24" />
      <element name="CONNECTIONTYPESECTION" value="True" />
      <element name="CustomFields" />
      <element name="EchoData">
        <element name="0000" value="8220000000000000" />
        <element name="0001" value="0400000100000000" />
        <element name="0007" value="0805193143" />
        <element name="MessageType" value="5678" />
      </element>
      <element name="ENABLESSL" value="true" />' );

我希望能够选择给定元素名称的值。就像是 ...

SELECT ID, Data.CONNECTIONTYPESECTION, Data.EchoData.0007, Data.EchoData.MessageType, Data.ENABLESSL
FROM TextXML
WHERE Data.Rownum = "Row23" AND Data.EchoData.MessageType = "1234".

ID  Data.CONNECTIONTYPESECTION Data.EchoData.0007 Data.EchoData.MessageType Data.ENABLESSL
1   True                       0805193143         1234                      true

我可以使用 CHARINDEX 和 SUBSTRING 但必须对“ name ”值的长度进行硬编码，并且假定“ value ”的长度始终相同。我希望有更好，更有效的方法来做到这一点。

我已经尝试过 CROSS APPPLY 和 XML VALUE 函数，但这只会返回 Data 字段中的第一个元素。我也找不到如何搜索特定元素“名称”以获取其值

标签： sqlxmlxsdsql-server-2017

解决方案

请尝试以下 T-SQL。

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Data NVARCHAR(max));
INSERT INTO @tbl (Data) VALUES
(N'<element name="Rownum" value="Row23"/>
    <element name="CONNECTIONTYPESECTION" value="True"/>
    <element name="CustomFields"/>
    <element name="EchoData">
        <element name="0000" value="8220000000000000"/>
        <element name="0001" value="0400000100000000"/>
        <element name="0007" value="0805193143"/>
        <element name="MessageType" value="1234"/>
    </element>
    <element name="ENABLESSL" value="false"/>'),
(N'<element name="Rownum" value="Row24"/>
    <element name="CONNECTIONTYPESECTION" value="True"/>
    <element name="CustomFields"/>
    <element name="EchoData">
        <element name="0000" value="8220000000000000"/>
        <element name="0001" value="0400000100000000"/>
        <element name="0007" value="0805193143"/>
        <element name="MessageType" value="5678"/>
    </element>
    <element name="ENABLESSL" value="true"/>');
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT *
        , TRY_CAST(data AS XML) AS xmldata
    FROM @tbl
)
SELECT rs.ID
    , xmldata.value('(element[@name="CONNECTIONTYPESECTION"]/@value)[1]','VARCHAR(10)') AS CONNECTIONTYPESECTION
    , xmldata.value('(element[@name="EchoData"]/element[@name="0007"]/@value)[1]','VARCHAR(10)') AS [EchoData.0007]
    , xmldata.value('(element[@name="EchoData"]/element[@name="MessageType"]/@value)[1]','VARCHAR(10)') AS [EchoData.MessageType]
    , xmldata.value('(element[@name="ENABLESSL"]/@value)[1]','VARCHAR(10)') AS [Data.ENABLESSL]
FROM rs
WHERE xmldata.value('(element[@name="Rownum"]/@value)[1]','VARCHAR(30)') = 'Row23'
    AND xmldata.value('(element[@name="EchoData"]/element[@name="MessageType"]/@value)[1]','VARCHAR(10)') = '1234';

输出

+----+-----------------------+---------------+----------------------+----------------+
| ID | CONNECTIONTYPESECTION | EchoData.0007 | EchoData.MessageType | Data.ENABLESSL |
+----+-----------------------+---------------+----------------------+----------------+
|  1 | True                  |    0805193143 |                 1234 | false          |
+----+-----------------------+---------------+----------------------+----------------+

推荐阅读