問題描述
我有一個 XML，我需要一個特定的命名空間，根據(jù)節(jié)點(如帶有 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....
如果您想獲取數(shù)據(jù)，您必須尊重正在使用的 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.
而且:您選擇的節(jié)點(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 節(jié)點上，即使存在 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.
這篇關于如何在 SQL Server 的 XQuery 中獲取特定的 XML 命名空間的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！