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

Question

在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 

我可以使用CHARDINDEX和SUBSTRING，但必须硬编码">名称"；值，并且这将">值"；总是相同的长度。我希望有一种更好、更有效的方法来做到这一点。

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

Answer 1

请尝试以下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          |
+----+-----------------------+---------------+----------------------+----------------+