Importing XML into SQL Server DB - getting blank results

Few things to point out.

(1) Your XML was not well-formed, so I had to fix it.

(2) The article that you are referring to was relevant before SQL Server 2005 release. Starting from SQL Server 2005 onwards, it is using XQuery language, based on the w3c standards, to deal with the XML data type. Microsoft proprietary OPENXML and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with obsolete SQL Server 2000. That's why use of the .nodes()

(3) Namespaces always shall be taken into account.

XML

<?xml version="1.0" encoding="UTF-8"?>
<BizData xmlns:xsi="http://www.w3.org/2001/XMLSchemainstance"
         xmlns="urn:iso:std:iso:20022:tech:xsd:head.003.001.01"
         xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:head.003.001.01 head.003.001.01.xsd">
    <Hdr/>
    <Pyld>
        <Document xmlns:xsi="http://www.w3.org/2001/XMLSchemainstance"
                  xmlns="urn:iso:std:iso:20022:tech:xsd:auth.017.001.02"
                  xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:auth.017.001.02 auth.017.001.02_ESMAUG_FULINS_1.1.0.xsd">
            <FinInstrmRptgRefDataRpt>
                <RptHdr/>
                <RefData>
                    <FinInstrmGnlAttrbts>
                        <Id>DE000C3JALS0</Id>
                        <FullNm>EAA3 SI 20211220 CS</FullNm>
                        <ShrtNm>EEX/EUAA P AUCTION SPOT</ShrtNm>
                        <ClssfctnTp>ITNXXX</ClssfctnTp>
                        <NtnlCcy>EUR</NtnlCcy>
                        <CmmdtyDerivInd>false</CmmdtyDerivInd>
                    </FinInstrmGnlAttrbts>
                    <Issr>529900J0JGLSFDWNFC20</Issr>
                    <TradgVnRltdAttrbts>
                        <Id>XEER</Id>
                        <IssrReq>false</IssrReq>
                        <FrstTradDt>20150325T06:00:00Z</FrstTradDt>
                        <TermntnDt>20211220T23:59:59Z</TermntnDt>
                    </TradgVnRltdAttrbts>
                    <DerivInstrmAttrbts>
                        <PricMltplr>500</PricMltplr>
                        <AsstClssSpcfcAttrbts>
                            <Cmmdty>
                                <Pdct>
                                    <Envttl>
                                        <Emssns>
                                            <BasePdct>ENVR</BasePdct>
                                            <SubPdct>EMIS</SubPdct>
                                            <AddtlSubPdct>EUAA</AddtlSubPdct>
                                        </Emssns>
                                    </Envttl>
                                </Pdct>
                                <TxTp>OTHR</TxTp>
                                <FnlPricTp>EXOF</FnlPricTp>
                            </Cmmdty>
                        </AsstClssSpcfcAttrbts>
                    </DerivInstrmAttrbts>
                    <TechAttrbts>
                        <RlvntCmptntAuthrty>DE</RlvntCmptntAuthrty>
                        <PblctnPrd>
                            <FrDt>20190716</FrDt>
                        </PblctnPrd>
                        <RlvntTradgVn>XEER</RlvntTradgVn>
                    </TechAttrbts>
                </RefData>
                <RefData>
                    <FinInstrmGnlAttrbts>
                        <Id>DE000C3JALT8</Id>
                        <FullNm>T3PA SI 20211220 CS</FullNm>
                        <ShrtNm>EEX/EUA P AUCTION SPOT</ShrtNm>
                        <ClssfctnTp>ITNXXX</ClssfctnTp>
                        <NtnlCcy>EUR</NtnlCcy>
                        <CmmdtyDerivInd>false</CmmdtyDerivInd>
                    </FinInstrmGnlAttrbts>
                    <Issr>529900J0JGLSFDWNFC20</Issr>
                    <TradgVnRltdAttrbts>
                        <Id>XEER</Id>
                        <IssrReq>false</IssrReq>
                        <FrstTradDt>20150325T06:00:00Z</FrstTradDt>
                        <TermntnDt>20211220T23:59:59Z</TermntnDt>
                    </TradgVnRltdAttrbts>
                    <DerivInstrmAttrbts>
                        <PricMltplr>500</PricMltplr>
                        <AsstClssSpcfcAttrbts>
                            <Cmmdty>
                                <Pdct>
                                    <Envttl>
                                        <Emssns>
                                            <BasePdct>ENVR</BasePdct>
                                            <SubPdct>EMIS</SubPdct>
                                            <AddtlSubPdct>EUAE</AddtlSubPdct>
                                        </Emssns>
                                    </Envttl>
                                </Pdct>
                                <TxTp>OTHR</TxTp>
                                <FnlPricTp>EXOF</FnlPricTp>
                            </Cmmdty>
                        </AsstClssSpcfcAttrbts>
                    </DerivInstrmAttrbts>
                    <TechAttrbts>
                        <RlvntCmptntAuthrty>DE</RlvntCmptntAuthrty>
                        <PblctnPrd>
                            <FrDt>20190716</FrDt>
                        </PblctnPrd>
                        <RlvntTradgVn>XEER</RlvntTradgVn>
                    </TechAttrbts>
                </RefData>
                <RefData>
                    <FinInstrmGnlAttrbts>
                        <Id>DE000F5PP026</Id>
                        <FullNm>SEME SI 20211220 CS</FullNm>
                        <ShrtNm>EEX/F 20211220</ShrtNm>
                        <ClssfctnTp>ITNXXX</ClssfctnTp>
                        <NtnlCcy>EUR</NtnlCcy>
                        <CmmdtyDerivInd>true</CmmdtyDerivInd>
                    </FinInstrmGnlAttrbts>
                    <Issr>529900J0JGLSFDWNFC20</Issr>
                    <TradgVnRltdAttrbts>
                        <Id>XEER</Id>
                        <IssrReq>false</IssrReq>
                        <FrstTradDt>20150325T06:00:00Z</FrstTradDt>
                        <TermntnDt>20211220T23:59:59Z</TermntnDt>
                    </TradgVnRltdAttrbts>
                    <DerivInstrmAttrbts>
                        <PricMltplr>1000</PricMltplr>
                        <AsstClssSpcfcAttrbts>
                            <Cmmdty>
                                <Pdct>
                                    <Envttl>
                                        <Emssns>
                                            <BasePdct>ENVR</BasePdct>
                                            <SubPdct>EMIS</SubPdct>
                                            <AddtlSubPdct>EUAE</AddtlSubPdct>
                                        </Emssns>
                                    </Envttl>
                                </Pdct>
                                <TxTp>OTHR</TxTp>
                                <FnlPricTp>EXOF</FnlPricTp>
                            </Cmmdty>
                        </AsstClssSpcfcAttrbts>
                    </DerivInstrmAttrbts>
                    <TechAttrbts>
                        <RlvntCmptntAuthrty>DE</RlvntCmptntAuthrty>
                        <PblctnPrd>
                            <FrDt>20181204</FrDt>
                        </PblctnPrd>
                        <RlvntTradgVn>XEER</RlvntTradgVn>
                    </TechAttrbts>
                </RefData>
                <RefData>
                    <FinInstrmGnlAttrbts>
                        <Id>DE000F5PPQ38</Id>
                        <FullNm>SEMA SI 20211220 CS</FullNm>
                        <ShrtNm>EEX/F 20211220</ShrtNm>
                        <ClssfctnTp>ITNXXX</ClssfctnTp>
                        <NtnlCcy>EUR</NtnlCcy>
                        <CmmdtyDerivInd>true</CmmdtyDerivInd>
                    </FinInstrmGnlAttrbts>
                    <Issr>529900J0JGLSFDWNFC20</Issr>
                    <TradgVnRltdAttrbts>
                        <Id>XEER</Id>
                        <IssrReq>false</IssrReq>
                        <FrstTradDt>20150325T06:00:00Z</FrstTradDt>
                        <TermntnDt>20211220T23:59:59Z</TermntnDt>
                    </TradgVnRltdAttrbts>
                    <DerivInstrmAttrbts>
                        <PricMltplr>1000</PricMltplr>
                        <AsstClssSpcfcAttrbts>
                            <Cmmdty>
                                <Pdct>
                                    <Envttl>
                                        <Emssns>
                                            <BasePdct>ENVR</BasePdct>
                                            <SubPdct>EMIS</SubPdct>
                                            <AddtlSubPdct>EUAA</AddtlSubPdct>
                                        </Emssns>
                                    </Envttl>
                                </Pdct>
                                <TxTp>OTHR</TxTp>
                                <FnlPricTp>EXOF</FnlPricTp>
                            </Cmmdty>
                        </AsstClssSpcfcAttrbts>
                    </DerivInstrmAttrbts>
                    <TechAttrbts>
                        <RlvntCmptntAuthrty>DE</RlvntCmptntAuthrty>
                        <PblctnPrd>
                            <FrDt>20181204</FrDt>
                        </PblctnPrd>
                        <RlvntTradgVn>XEER</RlvntTradgVn>
                    </TechAttrbts>
                </RefData>
                <RefData>
                    <FinInstrmGnlAttrbts>
                        <Id>DE000F5PPYW2</Id>
                        <FullNm>SEMC SI 20211220 CS</FullNm>
                        <ShrtNm>EEX/F 20211220</ShrtNm>
                        <ClssfctnTp>ITNXXX</ClssfctnTp>
                        <NtnlCcy>EUR</NtnlCcy>
                        <CmmdtyDerivInd>true</CmmdtyDerivInd>
                    </FinInstrmGnlAttrbts>
                    <Issr>529900J0JGLSFDWNFC20</Issr>
                    <TradgVnRltdAttrbts>
                        <Id>XEER</Id>
                        <IssrReq>false</IssrReq>
                        <FrstTradDt>20150325T06:00:00Z</FrstTradDt>
                        <TermntnDt>20211220T23:59:59Z</TermntnDt>
                    </TradgVnRltdAttrbts>
                    <DerivInstrmAttrbts>
                        <PricMltplr>1000</PricMltplr>
                        <AsstClssSpcfcAttrbts>
                            <Cmmdty>
                                <Pdct>
                                    <Envttl>
                                        <Emssns>
                                            <BasePdct>ENVR</BasePdct>
                                            <SubPdct>EMIS</SubPdct>
                                            <AddtlSubPdct>CERE</AddtlSubPdct>
                                        </Emssns>
                                    </Envttl>
                                </Pdct>
                                <TxTp>OTHR</TxTp>
                                <FnlPricTp>EXOF</FnlPricTp>
                            </Cmmdty>
                        </AsstClssSpcfcAttrbts>
                    </DerivInstrmAttrbts>
                    <TechAttrbts>
                        <RlvntCmptntAuthrty>DE</RlvntCmptntAuthrty>
                        <PblctnPrd>
                            <FrDt>20181204</FrDt>
                        </PblctnPrd>
                        <RlvntTradgVn>XEER</RlvntTradgVn>
                    </TechAttrbts>
                </RefData>
            </FinInstrmRptgRefDataRpt>
        </Document>
    </Pyld>
</BizData>

SQL, method #1

-- DDL and sample population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, XMLData XML, LoadedDateTime DATETIME DEFAULT GETDATE());

INSERT INTO @tbl (XMLData)
SELECT TRY_CAST(BulkColumn AS XML) AS BulkColumn 
FROM OPENROWSET(BULK 'e:\temp\OpenXMLTesting.xml', SINGLE_BLOB) AS x;
-- DDL and sample population, start

;WITH XMLNAMESPACES (DEFAULT 'urn:iso:std:iso:20022:tech:xsd:auth.017.001.02'
    , 'urn:iso:std:iso:20022:tech:xsd:head.003.001.01' AS ns1)
SELECT c.value('(FinInstrmGnlAttrbts/Id/text())[1]','VARCHAR(30)') AS FinInstrmGnlAttrbts_Id
    , c.value('(TradgVnRltdAttrbts/Id/text())[1]','VARCHAR(30)') AS TradgVnRltdAttrbts_Id
FROM @tbl AS tbl
    CROSS APPLY tbl.xmldata.nodes('/ns1:BizData/ns1:Pyld/Document/FinInstrmRptgRefDataRpt/RefData') AS t(c);

SQL, method #2

-- Method #2, directly from the XML file as a virtual DB table on the file system
;WITH XMLNAMESPACES (DEFAULT 'urn:iso:std:iso:20022:tech:xsd:auth.017.001.02'
    , 'urn:iso:std:iso:20022:tech:xsd:head.003.001.01' AS ns1), rs (xmldata) AS
(
    SELECT TRY_CAST(BulkColumn AS XML) AS BulkColumn 
    FROM OPENROWSET(BULK 'e:\temp\OpenXMLTesting.xml', SINGLE_BLOB) AS x
)
SELECT c.value('(FinInstrmGnlAttrbts/Id/text())[1]','VARCHAR(30)') AS FinInstrmGnlAttrbts_Id
    , c.value('(TradgVnRltdAttrbts/Id/text())[1]','VARCHAR(30)') AS TradgVnRltdAttrbts_Id
FROM rs AS tbl
    CROSS APPLY tbl.xmldata.nodes('/ns1:BizData/ns1:Pyld/Document/FinInstrmRptgRefDataRpt/RefData') AS t(c);

Output

+------------------------+-----------------------+
| FinInstrmGnlAttrbts_Id | TradgVnRltdAttrbts_Id |
+------------------------+-----------------------+
| DE000C3JALS0           | XEER                  |
| DE000C3JALT8           | XEER                  |
| DE000F5PP026           | XEER                  |
| DE000F5PPQ38           | XEER                  |
| DE000F5PPYW2           | XEER                  |
+------------------------+-----------------------+

Unfortunately XML namespaces is one area where the Microsoft documentation on sp_xml_preparedocument and OpenXML is woefully lacking detail. All of the current examples show anonymous XML. The nodes and query documentations are much better in this regard.

The XML document you're trying to parse contains XML namespace declarations on the BizData and Document elements. You'll need to take those into account:

• when calling sp_xml_preparedocument you'll need to supply an @xpath_namespaces parameter, and
• when calling OpenXML you'll need to supply namespace prefixes in the XPath expression as well as the with schema definitions.

For example:

declare
  @hDoc int,
  @xml xml =
N'<BizData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:iso:std:iso:20022:tech:xsd:head.003.001.01" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:head.003.001.01 head.003.001.01.xsd">
  <Pyld>
    <Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:iso:std:iso:20022:tech:xsd:auth.017.001.02" xsi:schemaLocation="urn:iso:std:iso:20022:tech:xsd:auth.017.001.02 auth.017.001.02_ESMAUG_FULINS_1.1.0.xsd">
      <FinInstrmRptgRefDataRpt>
        <RefData>
          <FinInstrmGnlAttrbts>
            <Id>DE000C3JALS0</Id>
          </FinInstrmGnlAttrbts>
        </RefData>
      </FinInstrmRptgRefDataRpt>
    </Document>
  </Pyld>
</BizData>';

-- Use sp_xml_preparedocument with an xpath namespaces declaration.
-- Here we define:
-- namespace prefix a = urn:iso:std:iso:20022:tech:xsd:head.003.001.01
-- namespace prefix b = urn:iso:std:iso:20022:tech:xsd:auth.017.001.02
-- REF: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-xml-preparedocument-transact-sql
exec sp_xml_preparedocument
  @hDoc output,
  @xml,
  N'<root xmlns:a="urn:iso:std:iso:20022:tech:xsd:head.003.001.01" xmlns:b="urn:iso:std:iso:20022:tech:xsd:auth.017.001.02" />'


-- Use OpenXML with namespace qualified paths.
-- i.e.: We use the 'a:' and 'b:' namespace prefixes defined in sp_xml_preparedocument to qualify the XPath expressions.
-- REF: https://learn.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql
select *
from OpenXML(@hDoc, '/a:BizData/a:Pyld/b:Document/b:FinInstrmRptgRefDataRpt/b:RefData/b:FinInstrmGnlAttrbts')
with ( Id [varchar](50) 'b:Id' );

exec sp_xml_removedocument @hDoc;

Which yields the result:

Id
DE000C3JALS0

HTH.