在SQL Server中动态选择XML中的多个值

Question

我有许多结构完全不同的大型XML文档，其中包含许多不同层次的元素，我想从中选择几十个项目。问题是，在完全不同的名称空间中有许多不同的文档类型，我都有类似的信息。所以我想让选择数据驱动。

我有一个表，我们叫它metadata。假设f1是某人的姓名，f2是他们的帐号，f3是电话号码，等等:

doctype    fldid     xpath    
---------  --------  --------------------------
abc        f1        /document/g/h[1]
abc        f2        /document/k/j/p/r/p[1]
abc        f3        /document/a/e/d[1]
def        f1        /info/d[1]
def        f2        /info/r/e/d[1]
def        f3        /info/e/s[1]

我们有数据表，叫做mydatatable:

docid    doctype    docfield
-------  ---------  ------------------------
1        abc        <document><n>.....

所以我想我可以做一个查询:

select 
metadata.fldid as fldid, 
mydatatable.docfield.value(metadata.xpath, 'nvarchar(max)') as data
from 
mydatatable
inner join 
metadata on mydatatable.doctype = metadata.doctype
where 
mydatatable.docid = 1

我正在等待输出:

fldid   data
------  --------------
f1      Tom
f2      344534534
f3      999-555-1212

但是，我得到了这个错误:

XML数据类型方法的参数1 "value"必须是字符串字面值。

所以这意味着我需要提前知道所有的文档结构，或者我需要游标遍历元数据表并将查询放入动态SQL中以创建此查询?

请注意，我不是在谈论具有相同元素名称的多个节点。我指的是具有不同(或相似)xpath的所有不同元素名。

Answer 1

是的，您需要动态SQL来做到这一点。但是您不需要游标，只需构建查询并一次执行即可。

我认为你可能只是建立一系列的if elseXQuery表达式，在你的情况下，代码将产生

if(sql:column("md.xpath") = "/document/g/h[1]") then '/document/g/h[1]'
else if(sql:column("md.xpath") = "/document/k/j/p/r/p[1]") then '/document/k/j/p/r/p[1]'
else if(sql:column("md.xpath") = "/document/a/e/d[1]") then '/document/a/e/d[1]'
else if(sql:column("md.xpath") = "/info/d[1]") then '/info/d[1]'
else if(sql:column("md.xpath") = "/info/r/e/d[1]") then '/info/r/e/d[1]'
else if(sql:column("md.xpath") = "/info/e/s[1]") then '/info/e/s[1]'
else ()

可以这样使用动态SQL:

我已经参数化了@docid，你不需要这样做，你可以像你所做的那样输入一个文字，但不要直接从其他地方注入数据。

注意正确使用QUOTENAME转义字符串

DECLARE @xq nvarchar(max) = (
SELECT STRING_AGG(CAST(
'if(sql:column("md.xpath") = ' + QUOTENAME(md.xpath, '"') + ') then ' + QUOTENAME(md.xpath, '''')
AS nvarchar(max)), '
else ')
+ '
else ()'
FROM metadata md
);
DECLARE @sql nvarchar(max) = N'
select
md.fldid,
dt.docfield.value(''(
' + @xq + ''')[1]'',''nvarchar(max)'') as data
from mydatatable dt
inner join metadata md on dt.doctype = md.doctype
where dt.docid = @docid;
';
PRINT @sql;  -- for testing
DECLARE @docid int = 1;
EXEC @sp_executesql
@sql,
N'@docid int',
@docid = @docid;