CODEWITHSUNDEEP
sql-serverxmlt-sqlshred
Matt
Matt

Reputation: 1

Importing XML into SQL Server DB - getting blank results

I am trying to import a publicly available set of data into an SQL Server DB. I am a newbie, and am struggling with this - spent most of today on this problem... It returns no results, no matter how much tweaking I do. The examples available online, I can follow (and work), but when I try to use the actual data I have, I get no results. Any help with this is much appreciated.

I am following the steps in the following URL :

https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/

The SQL I am using is as follows (my XML file is called D:\OpenXMLTesting.xml):

    CREATE DATABASE OPENXMLTesting
GO

USE OPENXMLTesting
GO

CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'D:\OpenXMLTesting.xml', SINGLE_BLOB) AS x;

SELECT * FROM XMLwithOpenXML




DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT Id
FROM OPENXML(@hDoc, 'BizData/Pyld/Document/FinInstrmRptgRefDataRpt/RefData/FinInstrmGnlAttrbts')
WITH 
(
Id [varchar](50) '@Id'
)

EXEC sp_xml_removedocument @hDoc
GO

The raw XML contains the following data :-

<?xml version="1.0" encoding="UTF-8"?>

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


+<Hdr>


































































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


+<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>2015-03-25T06:00:00Z</FrstTradDt>

<TermntnDt>2021-12-20T23: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>2019-07-16</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>2015-03-25T06:00:00Z</FrstTradDt>

<TermntnDt>2021-12-20T23: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>2019-07-16</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>2015-03-25T06:00:00Z</FrstTradDt>

<TermntnDt>2021-12-20T23: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>2018-12-04</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>2015-03-25T06:00:00Z</FrstTradDt>

<TermntnDt>2021-12-20T23: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>2018-12-04</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>2015-03-25T06:00:00Z</FrstTradDt>

<TermntnDt>2021-12-20T23: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>2018-12-04</FrDt>

</PblctnPrd>

<RlvntTradgVn>XEER</RlvntTradgVn>

</TechAttrbts>

</RefData>

</FinInstrmRptgRefDataRpt>

</Document>

</Pyld>

</BizData>

Any help much appreciated.

Thanks

Upvotes: 0

Views: 399

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22177

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

Upvotes: 1

AlwaysLearning
AlwaysLearning

Reputation: 8809

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.

Upvotes: 0

Related Questions