无法使用SQL/OPENXML查询XML文档

Question

我想使用SQL查询以下xml文件:

<?xml version="1.0" encoding="UTF-8"?>
<GL_MarketDocument xmlns="urn:iec62325.351:tc57wg16:451-6:generationloaddocument:3:0">
<mRID>2f6f8b82348440b1b121bca06311945d</mRID>
<time_Period.timeInterval>
<start>2020-03-02T23:00Z</start>
<end>2020-03-03T18:30Z</end>
</time_Period.timeInterval>
</GL_MarketDocument>

使用下面的代码，我想获得"mRID":

的值

DECLARE @DocHandle int  
DECLARE @XmlDocument varchar(MAX)  
SELECT @XMLDocument=I
FROM OPENROWSET (BULK 'TP_10V1001C--00013H_ENTSOE-ETP__00a8f07d-95bd-4075-b1f7-3f54ce6162f3.xml', SINGLE_BLOB) as ImportFile(I)
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument, N'<root xmlns:d="urn:iec62325.351:tc57wg16:451-6:generationloaddocument:3:0"/>' ; 
SELECT *  
FROM OPENXML (@DocHandle, N'/d:GL_MarketDocument')  
WITH ([mRID] varchar(50))  
EXEC sp_xml_removedocument @DocHandle

然而，结果是:

<表类>mRIDtbody><<tr>空

保留Microsoft专有的OPENXML及其同伴sp_xml_preparedocument和sp_xml_removedocument只是为了向后兼容过时的SQL Server 2000。它们的使用减少了，只在极少数边缘情况下使用。

从SQL Server 2005开始，最好使用基于w3c标准的XQuery语言，同时处理XML数据类型。

您的XML有一个默认的名称空间，因此应该考虑到它。

SQL，来自变量

DECLARE @XMLDocument XML =
N'<GL_MarketDocument xmlns="urn:iec62325.351:tc57wg16:451-6:generationloaddocument:3:0">
<mRID>2f6f8b82348440b1b121bca06311945d</mRID>
<time_Period.timeInterval>
<start>2020-03-02T23:00Z</start>
<end>2020-03-03T18:30Z</end>
</time_Period.timeInterval>
</GL_MarketDocument>';
WITH XMLNAMESPACES (DEFAULT 'urn:iec62325.351:tc57wg16:451-6:generationloaddocument:3:0')
SELECT c.value('(mRID/text())[1]','NVARCHAR(100)') AS mRID
FROM @XMLDocument.nodes('/GL_MarketDocument') AS t(c);

SQL，直接来自XML文件

WITH XMLNAMESPACES (DEFAULT 'urn:iec62325.351:tc57wg16:451-6:generationloaddocument:3:0')
, rs (xmlData) AS
(
SELECT TRY_CAST(BulkColumn AS XML) 
FROM OPENROWSET(BULK N'e:TempTP_10V1001C--00013H_ENTSOE-ETP__00a8f07d-95bd-4075-b1f7-3f54ce6162f3.xml', SINGLE_BLOB) AS x
)
SELECT c.value('(mRID/text())[1]','NVARCHAR(100)') AS mRID
FROM rs 
CROSS APPLY xmlData.nodes('/GL_MarketDocument') AS t(c);

+----------------------------------+
|               mRID               |
+----------------------------------+
| 2f6f8b82348440b1b121bca06311945d |
+----------------------------------+