如何从SQL查询读取XML节点 [英] How to read XML node from SQL query

问题描述

需要基于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屋！