Импорт XML в базу данных SQL Server - получение пустых результатов

Несколько вещей, чтобы указать.

(1) Ваш XML был неправильно сформирован, поэтому мне пришлось его исправить.

(2) Статья, на которую вы ссылаетесь, была актуальна до выпуска SQL Server 2005. Начиная с SQL Server 2005, он использует язык XQuery, основанный на стандартах w3c, для работы с типом данных XML. Проприетарный Microsoft OPENXML и его компаньоны sp_xml_preparedocument и sp_xml_removedocument сохранены только для обратной совместимости с устаревшим SQL Server 2000. Вот почему использование .nodes()

(3) Пространства имен всегда должны учитываться.

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, способ №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, способ №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);

Выход

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

К сожалению, пространства имен XML — это одна из областей, в которой документации Microsoft по sp_xml_preparedocument и OpenXML крайне не хватает подробностей. Все текущие примеры показывают анонимный XML. Документация nodes и query намного лучше в этом отношении.

Документ XML, который вы пытаетесь проанализировать, содержит объявления пространств имен XML в элементах BizData и Document. Вам нужно будет принять их во внимание:

• при вызове sp_xml_preparedocument вам нужно указать параметр @xpath_namespaces и
• при вызове OpenXML вам нужно указать префиксы пространств имен в выражении XPath, а также определения схемы with.

Например:

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://docs.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://docs.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;

Что дает результат:

Id
DE000C3JALS0

ХТН.