如何在 SQL Server 的 XQuery 中获取特定的 XML 命名空间
我有一个 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.
相关文章