从 SQL XML 字段中提取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4855476/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Extract a value from SQL XML field
提问by Mike Hanson
Using MS SQL Server, I've got some data in an XML field (called XML), which is structured like this:
使用 MS SQL Server，我在 XML 字段（称为XML）中获得了一些数据，其结构如下：
<Transaction01>
<TransactionSetPurpose>Insert</TransactionSetPurpose>
<POHeader>
<PO_NBR>LG40016181</PO_NBR>
</POHeader>
</Transaction01>
I'm trying to create a SQL query to fetch another column called SubmittedDate, along with the PO_NBR from this XML field. Being new to XPath, I've read numerous examples and tried both queryand value, but I've not been successful yet. For example:
我正在尝试创建一个 SQL 查询来获取另一个名为 SubmittedDate 的列，以及这个 XML 字段中的 PO_NBR。作为 XPath 的新手，我阅读了许多示例并尝试了query和value，但我还没有成功。例如：
SELECT SubmittedDate,
XML.query('data(/POHeader/PO_NBR)') as PO_NBR
FROM SubmitXML
This just gives me a empty column. After getting a working test from Quassnoi, I worked from his XML to mine, and discovered the problem is the xmlns and xmlns:i attributes in the root node:
这只是给了我一个空列。从 Quassnoi 获得工作测试后，我从他的 XML 开始工作，发现问题是根节点中的 xmlns 和 xmlns:i 属性：
<Transaction01 xmlns="http://services.iesltd.com/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
So how do I get around that?
那么我该如何解决呢？
采纳答案by Quassnoi
SELECT SubmittedDate,
XML.query('data(/Transaction01/POHeader/PO_NBR)') as PO_NBR
FROM SubmitXML
You original XPath, /POHeader/PO_NBR, assumed that POHeaderis the root node (which is not).
您原来的XPath,/POHeader/PO_NBR假定这POHeader是根节点（不是）。
A sample query to check:
要检查的示例查询：
DECLARE @myxml XML
SET @myxml = '
<Transaction01>
<TransactionSetPurpose>Insert</TransactionSetPurpose>
<POHeader>
<PO_NBR>LG40016181</PO_NBR>
</POHeader>
</Transaction01>'
SELECT @myxml.query('data(/Transaction01/POHeader/PO_NBR)')
If Transaction01is not always the root node (which is not a good thing), use this:
如果Transaction01不总是根节点（这不是一件好事），请使用：
SELECT SubmittedDate,
XML.query('data(/*/POHeader/PO_NBR)') as PO_NBR
FROM SubmitXML
Generally, XMLschema assumes that the tag names are fixed and the variable parts go to the data of the nodes and the attributes rather than into their names, like this:
通常，XMLschema 假设标签名称是固定的，并且可变部分转到节点和属性的数据而不是它们的名称，如下所示：
<Transaction id='01'>
<TransactionSetPurpose>Insert</TransactionSetPurpose>
<POHeader>
<PO_NBR>LG40016181</PO_NBR>
</POHeader>
</Transaction>
Update:
更新：
You should declare the namespaces using WITH XMLNAMESPACES:
您应该使用WITH XMLNAMESPACES以下方法声明命名空间：
DECLARE @myxml XML
SET @myxml = '
<Transaction01 xmlns="http://services.iesltd.com/" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<TransactionSetPurpose>Insert</TransactionSetPurpose>
<POHeader>
<PO_NBR>LG40016181</PO_NBR>
</POHeader>
</Transaction01>'
;
WITH XMLNAMESPACES
(
'http://services.iesltd.com/' AS m
)
SELECT @myxml.query
(
'data(/*/m:POHeader/m:PO_NBR)'
)
Update 2:
更新 2：
To sort:
排序：
;
WITH XMLNAMESPACES
(
'http://services.iesltd.com/' AS m
)
SELECT SubmittedDate,
XML.value('(/*/m:POHeader/m:PO_NBR)[1]', 'NVARCHAR(200)') AS po_nbr
FROM SubmitXML
ORDER BY
po_nbr
回答by Chandu
If you want to avoid hardcoding Transaction01, you can try this:
如果你想避免硬编码 Transaction01，你可以试试这个：
SELECT SubmittedDate,
XML.query('data(//POHeader/PO_NBR)') as PO_NBR
FROM SubmitXML
E.G:
例如：
CREATE TABLE #TEMP (XMLTEXT XML)
INSERT INTO #TEMP
SELECT '<Transaction01> <TransactionSetPurpose>Insert</TransactionSetPurpose> <POHeader> <PO_NBR>LG40016181</PO_NBR> </POHeader> </Transaction01> '
SELECT XMLTEXT.query('data(//POHeader/PO_NBR)') as PO_NBR
FROM #TEMP
