如何在 SQL Server 的 XQuery 中获取特定的 XML 命名空间

2021-10-01 00:00:00 xml sql xquery sql-server

我有一个 XML，我需要一个特定的命名空间，根据节点(如带有 hls 的 temprature)，我需要该http://www.schema.hls.com/extension"我已经尝试过这些

I have a XML that I need one specific namespace according to node like temprature with hls i need namespace of that "http://www.schema.hls.com/extension" I have tried with these

DECLARE @EventXML AS XML

SET @EventXML='<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns:test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ns="urn:global:test:xsd:1" 
xmlns:hls="http://schema.hls.com/extension" creationDate="2007-01-25T00:00:00Z"
schemaVersion="1.0">
<TestBody>
<TestList>
<TestEvent>
    <hls:temperature>20</hls:temperature>
  </TestEvent>
</TestList>
</TestBody>
</ns:test>'

SELECT 
OE.value('@ns','varchar(50)') + '#' + OE.value('fn:local-name(.)[1]','varchar(50)'),
OE.value('@id','varchar(50)'),
CONVERT(VARCHAR(4000),CASE WHEN OE.exist('./*') =1 THEN OE.query('./*')  ELSE    
OE.value('./text()[1]','varchar(100)') END)
FROM @EventXML.nodes('//TestEvent/*') TestEvent(OE)
WHERE OE.value('fn:local-name(.)[1]','varchar(50)') IN --(@tag) 
(SELECT  Split.a.value('.', 'VARCHAR(100)') AS extag 
FROM  (SELECT   CONVERT(XML,'<M>' + REPLACE(ISNULL('temperature','0'), ',', '</M><M>') + '</M>') AS String 
 ) AS A CROSS APPLY String.nodes ('/M') AS Split(a))

我在 SQL 查询窗口中使用这些，但只获得第三列值 20 没有通过@ns 获得命名空间

I am using these in SQL query window but getting only third column value 20 not get namespace by @ns

请建议如何获取命名空间

Please suggest how to get the namespace 

OE.value('@ns','varchar(50)')

通过这些.

提前致谢.

推荐答案

不知何故，您的代码和 XML 不太匹配 - 并且查询真的很混乱....

Your code and XML somehow just don't quite match up - and the query is really quite confusing....

如果您想获取数据，您必须尊重正在使用的 XML 命名空间.您需要使用 WITH XMLNAMESPACES() 构造声明它们，并且需要在 XPath 中使用它们.

If you want to fetch the data, you must respect the XML namespaces in play. You need to declare them with a WITH XMLNAMESPACES() construct, and you need to use them in your XPath.

而且:您选择的节点()实际上没有任何id和ns 属性..... 所以当然你没有得到任何值！

But also: the node you're selecting (<hls:temperature>) doesn't really have any id and ns attributes..... so of course you're not getting any values!

我尝试使用精简版并添加了两个属性 - 只是为了展示如何在代码中使用 XML 命名空间.

I tried to use a trimmed down version and I added the two attributes - just to show how to use the XML namespaces stuff in your code.

来了:

DECLARE @EventXML AS XML

SET @EventXML = 
   '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <ns:test xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xmlns:ns="urn:global:test:xsd:1" 
             xmlns:hls="http://schema.hls.com/extension" 
             creationDate="2007-01-25T00:00:00Z" schemaVersion="1.0">
       <TestBody>
          <TestList>
             <TestEvent>
                <hls:temperature ns="test" id="42">20</hls:temperature>
             </TestEvent>
         </TestList>
      </TestBody>
   </ns:test>'

-- define your XML namespaces that are in play. 
-- You *MUST* match the namespace definition, but the *prefixes* that you define
-- can be something else entirely than in the XML document!
-- Of course, inside your XPath, you *MUST* use the defined prefixes!
;WITH XMLNAMESPACES('urn:global:test:xsd:1' AS x1, 
                    'http://schema.hls.com/extension' AS x2)
SELECT 
    OE.value('@ns', 'varchar(50)'),
    OE.value('@id', 'varchar(50)')
FROM 
    @EventXML.nodes('/x1:test/TestBody/TestList/TestEvent/x2:*') TestEvent(OE)

此代码 - 使用在您的 XML 中定义和使用的 XML 命名空间 - 产生以下输出:

This code - using the XML namespaces defined and used in your XML - produces this output:

(No column name)  (No column name)
test                   42

所以这显示了如何访问属性 - 如果它们存在！- 在您的 XML 节点上，即使存在 XML 命名空间.

So this shows how you can access the attributes - if they are present! - on your XML nodes, even with the presence of XML namespaces.

相关文章