使用多个命名空间从XML值创建where条件

Question

我希望从XML中返回where条件的值。我想从消息表的请求列返回一个值。它采用XML数据格式。不幸的是，我所能取得的只是一无所获。然后我试着把这个值作为一列，但的值总是为null

这里有一个来自请求列的XML：

<InvoiceRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/InternalReuqests">
<ActiveUserID xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">0</ActiveUserID>
<LinqConfigId xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">0</LinqConfigId>
<RequestHeaderInfo xmlns:d2p1="Fix.Services" xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">
<d2p1:MapArchive i:nil="true" />
<d2p1:HandledSuccessCategory>rscNone</d2p1:HandledSuccessCategory>
</RequestHeaderInfo>
<Username xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase" i:nil="true" />
<SSID>S-1-6-25-123456789-123456789-123456789-12345</SSID>
<miscdata xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
<d2p1:string>date:2020.02.26 08:27:00</d2p1:string>
<d2p1:string>hours:0</d2p1:string>
<d2p1:string>Ready:True</d2p1:string>
<d2p1:string>disct:False</d2p1:string>
<d2p1:string>extdisct:False</d2p1:string>
<d2p1:string>Matmove:False</d2p1:string>
<d2p1:string>Matlim:0</d2p1:string>
<d2p1:string>Comments:</d2p1:string>
</miscdata>
<ffreeID>468545</ffreeID>
</InvoiceRequest>

这是我的sql查询：

select id, Request.value('(/*:InvoiceRequest/*:ffreeID)[1]','varchar(max)')
from messages

我想我应该在第一列中获取数据库中的id，然后在它旁边获取ffreeID的值，但Request.value始终为null。

有人能查一下吗？我缺了什么？

Answer 1

您需要声明默认名称空间，对于您的xml，它是http://schemas.datacontract.org/2004/07/InternalReuqests:

--Sample XML
DECLARE @xml xml = '<InvoiceRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/InternalReuqests">
<ActiveUserID xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">0</ActiveUserID>
<LinqConfigId xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">0</LinqConfigId>
<RequestHeaderInfo xmlns:d2p1="Fix.Services" xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">
<d2p1:MapArchive i:nil="true" />
<d2p1:HandledSuccessCategory>rscNone</d2p1:HandledSuccessCategory>
</RequestHeaderInfo>
<Username xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase" i:nil="true" />
<SSID>S-1-6-25-123456789-123456789-123456789-12345</SSID>
<miscdata xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
<d2p1:string>date:2020.02.26 08:27:00</d2p1:string>
<d2p1:string>hours:0</d2p1:string>
<d2p1:string>Ready:True</d2p1:string>
<d2p1:string>disct:False</d2p1:string>
<d2p1:string>extdisct:False</d2p1:string>
<d2p1:string>Matmove:False</d2p1:string>
<d2p1:string>Matlim:0</d2p1:string>
<d2p1:string>Comments:</d2p1:string>
</miscdata>
<ffreeID>468545</ffreeID>
</InvoiceRequest>'; --Assumed this should be </InvoiceRequest>, not <InvoiceRequest>.
--Get value
WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/InternalReuqests')
SELECT X.Request.value('(/InvoiceRequest/ffreeID/text())[1]','int')
FROM (VALUES(@XML))X(Request);

Answer 2

这里是模拟模拟表的另一种方法。其他一切都类似于@Larnu的解决方案。所有的功劳都归于@Larnu。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Request XML);
INSERT INTO @tbl (Request)
VALUES
(N'<InvoiceRequest xmlns:i="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://schemas.datacontract.org/2004/07/InternalReuqests">
<ActiveUserID xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">0</ActiveUserID>
<LinqConfigId xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">0</LinqConfigId>
<RequestHeaderInfo xmlns:d2p1="Fix.Services"
xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase">
<d2p1:MapArchive i:nil="true"/>
<d2p1:HandledSuccessCategory>rscNone</d2p1:HandledSuccessCategory>
</RequestHeaderInfo>
<Username xmlns="http://schemas.datacontract.org/2004/07/Fix.ServiceBase" i:nil="true"/>
<SSID>S-1-6-25-123456789-123456789-123456789-12345</SSID>
<miscdata xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
<d2p1:string>date:2020.02.26 08:27:00</d2p1:string>
<d2p1:string>hours:0</d2p1:string>
<d2p1:string>Ready:True</d2p1:string>
<d2p1:string>disct:False</d2p1:string>
<d2p1:string>extdisct:False</d2p1:string>
<d2p1:string>Matmove:False</d2p1:string>
<d2p1:string>Matlim:0</d2p1:string>
<d2p1:string>Comments:</d2p1:string>
</miscdata>
<ffreeID>468545</ffreeID>
</InvoiceRequest>');
-- DDL and sample data population, end
WITH XMLNAMESPACES(DEFAULT 'http://schemas.datacontract.org/2004/07/InternalReuqests')
SELECT ID
, c.value('(ffreeID/text())[1]','INT') AS ffreeID
FROM @tbl AS tbl
CROSS APPLY tbl.Request.nodes('/InvoiceRequest') AS t(c);

输出

+----+---------+
| ID | ffreeID |
+----+---------+
|  1 |  468545 |
+----+---------+