Unable to Query XML Document with SQL/OPENXML

I would like to query the following XML-File using SQL:

<?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>

Using this code I would like to get the value for “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

However, the result is:

mRID
NULL

How to get the correct value for mRID (‘2f6f8b82348440b1b121bca06311945d’) instead of NULL?

Answer

Microsoft proprietary OPENXML and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000. Their use is diminished just to very few fringe cases.

Starting from SQL Server 2005 onwards, it is better to use XQuery language, based on the w3c standards, while dealing with the XML data type.

Your XML has a default namespace, so it should be taken into account.

SQL, from a variable

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, directly from the XML file

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);

Output

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