sql-server - 从 SQL 子查询的 XML 下拉列表中返回选定值

Question

我有一个数据库，它在 XML 列中存储表单的结果。不幸的是，它还在开头存储了一些无法直接解析的额外字符，因此我需要在解析值之前删除它们。我的另一个限制是我用来显示报告的系统 (Xtraction) 只允许运行子查询。

尽管存在所有这些限制，但我有一个成功的方法可以从 XML 字段中获取文本值，但我似乎无法将其应用于下拉列表中的选定值。我的方法不仅返回所选值，还返回所有下拉选项以及非分隔字符串。

由于表单非常大，我删除了除多余字符和下面 XML 的下拉部分之外的所有内容:

_RCFM*=.hÞ<form><select id="Dropdown01" label="Substantive APS Level:"
mandatory="true" readonly="false" style="combo" type="string" 
visible="true">EL1<option label=""/><option 
label="APS5">APS5</option><option label="APS6">APS6</option><option 
label="EL1">EL1</option></select></form>

我使用的子查询是:

(SELECT  
x.CARTITEMID
, x.DROPDOWN01
FROM (
SELECT 
    CARTITEMID
   , CAST(RIGHT(cast(cast(SVCCARTITEMM1.OPTIONS as varbinary(max)) as varchar(max)),
   LEN(cast(cast(SVCCARTITEMM1.OPTIONS as varbinary(max)) as varchar(max)))
   -CHARINDEX('<form>',cast(cast(SVCCARTITEMM1.OPTIONS as varbinary(max)) as varchar(max)))+1)
   AS XML).value('(/form/select[@id="Dropdown01"])[1]', 'varchar(max)')[DROPDOWN01]

FROM SVCCARTITEMM1) x
)

上述子查询的结果是:EL1APS5APS6EL1

我只需要返回选定的文本，在本例中是字符串中的初始“EL1”。

请注意，从 CAST 到“AS XML”的代码通常是字段引用所在的位置，这就是我用来转换和删除提到的前导字符的代码。

请帮忙，我真的很努力地找到了解决这个相当晦涩的问题的方法。

Answer 1

节点的值(value)是它的全部内容。试试这个:

DECLARE @x XML=
'<x>hallo1
    <a>testa</a>
   hallo2
    <b>testb</b>
   hallo3
</x>';

SELECT x.value('.','nvarchar(max)') AS EntireContent
      ,x.value('(./text())[1]','nvarchar(max)') AS FirstText
      ,x.value('(./text())[2]','nvarchar(max)') AS SecondText
      ,x.value('(./a)[1]','nvarchar(max)') AS DeeperNode --might use `text()` here as well
FROM @x.nodes('/x') AS A(x);

您没有说明列的数据类型。由于有前导字符，这不能是XML。您的代码让我认为它是某种文本。我认为您的整个查询都可以简化:

DECLARE @YourTbl TABLE(OPTIONS NVARCHAR(MAX));
INSERT INTO @YourTbl VALUES
(N'_RCFM*=.hÞ<form><select id="Dropdown01" label="Substantive APS Level:"
mandatory="true" readonly="false" style="combo" type="string" 
visible="true">EL1<option label=""/><option 
label="APS5">APS5</option><option label="APS6">APS6</option><option 
label="EL1">EL1</option></select></form>');

SELECT Casted.value(N'(/form/select[@id="Dropdown01"]/text())[1]','nvarchar(max)')
FROM @YourTbl AS t
CROSS APPLY(SELECT CAST(SUBSTRING(t.OPTIONS,CHARINDEX('<',t.OPTIONS),10000) AS XML)) AS A(Casted)

如果您的列的数据类型是 IMAGE 或 TEXT(已弃用超过 10 年，但您的代码可能指向另一种选择)，请尝试以下操作:

SELECT AsXML.value(N'(/form/select[@id="Dropdown01"]/text())[1]','nvarchar(max)')
FROM @YourTbl AS t
CROSS APPLY(SELECT CAST(CAST(t.OPTIONS AS VARBINARY(MAX)) AS VARCHAR(MAX))) AS A(AsString)
CROSS APPLY(SELECT CAST(SUBSTRING(AsString,CHARINDEX('<',AsString),10000) AS XML)) AS B(AsXML)

注意 如果第二个抛出类似无效字符的错误，尝试在第一个CROSS APPLY中使用NVARCHAR > 代替 AS VARCHAR。