如何从SQL查询读取XML节点 [英] How to read XML node from SQL query
本文介绍了如何从SQL查询读取XML节点的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
需要基于XML数据中存在的Ref ID格式获取记录.
请帮助我进行sql查询
need to fetch records based on the form Ref ID which is there in XML data.
please help me with the sql query
<FormTest xmlns="xyz">
<NewForm xmlns:a="abc">
<a:Attachments />
<a:Comment i:nil="true" />
<a:FormRef xmlns:b="test">
<b:Id>36134fe3-9826-4c1d-bb79-13dcfda9e976</b:Id>
<b:Name>test form</b:Name>
<b:FormMasterId>00000000-0000-0000-0000-000000000000</b:FormMasterId>
<b:FormRefId>00000000-0000-0000-0000-000000000000</b:FormRefId>
<b:Type>known</b:Type>
<b:Version xmlns:c="system" i:nil="true" />
</a:FormRef>
</NewForm>
</FormTest>
我尝试过的事情:
What I have tried:
SELECT * FROM Events
CROSS APPLY [Body].nodes(''//*:FormRef/Id'') T(c)
where T.c.value(''.'', ''uniqueidentifier'') = ''E0FEAF8D-912C-4B0F-84A9-60E6C5C1EDE2''
推荐答案
您的XML包含多个名称空间.您需要在查询中包括这些内容:
Your XML contains multiple namespaces. You need to include those in your query:
WITH XMLNAMESPACES
(
'xyz' as ns1,
'abc' as a,
'test' as b
)
SELECT Events.*
FROM Events
CROSS APPLY [Body].nodes('//a:FormRef/b:Id') T(c)
where T.c.value('.', 'uniqueidentifier') = 'E0FEAF8D-912C-4B0F-84A9-60E6C5C1EDE2';
将命名空间添加到带有WITH XMLNAMESPACES的查询中| Microsoft文档 [ ^ ]
NB:我假设XML是较大文档的一部分.如果不是,则它无效-前缀"i"在任何地方都没有声明.
Add Namespaces to Queries with WITH XMLNAMESPACES | Microsoft Docs[^]
NB: I''m assuming that XML is a fragment of a larger document. If it''s not, then it''s not valid - the prefix "i" is not declared anywhere.
这篇关于如何从SQL查询读取XML节点的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文