键/值对的SQL XQuery选择器

Question

我在这样的列中有一些xml。

<ScopedWorkflowConfiguration xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/workflow/2012/xaml/activities">
<appSettings>
<AppSetting>
<Key>CurrentWebUri</Key>
<Value>http://myurl.org</Value>
</AppSetting>
<AppSetting>
<Key>SomethingElse</Key>
<Value>1</Value>
</AppSetting>
<AppSetting>
<Key>AnotherThing</Key>
<Value>420</Value>
</AppSetting>
</appSettings>
</ScopedWorkflowConfiguration>

正在尝试选择"值"节点的值。

;WITH Casted AS (SELECT t.Id, t.[Configuration] As XmlData FROM dbo.MyTable AS t)
Select Distinct
Id
,pv.value('//*:Key="CurrentWebUri"/Value', 'nvarchar(max)') As CurrentWebUri
From
Casted
Cross Apply Casted.XmlData.nodes(N'//*:ScopedWorkflowConfiguration//*:appSettings//*:AppSetting') AS A(pv)

我知道我很接近，但我在这里的语法有问题：//：Key="CurrentWebUri"/Value*

上述查询导致此错误：

/需要一个节点或一组节点

你能帮忙吗？

添加一个结果集供参考，以进一步澄清我的目标：

+----+------------------+------------------+------------------+
| ID | CurrentWebUri    | SomethingElse    |  AnotherThing    |
+----+------------------+------------------+------------------+
|  1 | example.org      |        1         |       420        |
+----+------------------+------------------+------------------+
|  2 | example.com      |        6         |       29         |
+----+------------------+------------------+------------------+

Answer 1

默认的命名空间声明丢失，XPath表达式关闭。随着对所需输出的最新澄清，需要一个用于PIVOTing的动态SQL。

SQL

-- DDL and sample data population, start
USE tempdb;
DROP TABLE IF EXISTS dbo.tbl;
CREATE TABLE dbo.tbl (ID INT IDENTITY PRIMARY KEY, [Configuration] xml);
INSERT INTO dbo.tbl ([Configuration])
VALUES ('<ScopedWorkflowConfiguration xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://schemas.microsoft.com/workflow/2012/xaml/activities">
<appSettings>
<AppSetting>
<Key>CurrentWebUri</Key>
<Value>http://myurl.org</Value>
</AppSetting>
<AppSetting>
<Key>SomethingElse</Key>
<Value>1</Value>
</AppSetting>
<AppSetting>
<Key>AnotherThing</Key>
<Value>420</Value>
</AppSetting>
</appSettings>
</ScopedWorkflowConfiguration>');
-- DDL and sample data population, end
DECLARE @columns AS NVARCHAR(MAX),
@SQL AS NVARCHAR(MAX);
;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/workflow/2012/xaml/activities')
SELECT TOP(1) @columns = STUFF([Configuration].query('for $s in /ScopedWorkflowConfiguration/appSettings/AppSetting/Key
return <x>{concat(",",$s)}</x>')
.value('.','varchar(max)'),1,1,'')
FROM dbo.tbl;
SET @SQL = 
N';WITH XMLNAMESPACES (DEFAULT ''http://schemas.microsoft.com/workflow/2012/xaml/activities''), rs AS
( 
SELECT ID
, c.value(''(Key/text())[1]'', ''NVARCHAR(MAX)'') AS [Key]
, c.value(''(Value/text())[1]'', ''NVARCHAR(MAX)'') AS [Value]
FROM dbo.tbl as tbl
CROSS APPLY tbl.[Configuration].nodes(''/ScopedWorkflowConfiguration/appSettings/AppSetting'') AS t(c)
)
SELECT ID,' + @columns + N' FROM rs
PIVOT (
MAX([Value])
FOR [Key] IN (' + @columns + N')
) AS pvt;
';
EXEC sp_executesql @SQL;

输出

+----+------------------+---------------+--------------+
| ID |  CurrentWebUri   | SomethingElse | AnotherThing |
+----+------------------+---------------+--------------+
|  1 | http://myurl.org |             1 |          420 |
+----+------------------+---------------+--------------+