Import nested XML file to SQL Server

Question

I have the following problem and have no idea how to import the data from a nested xml file to the database.

Here the example of the xml file:

<pm001 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="T1_pm001.xsd">
    <reportHeader>
        <exchName>Xetra</exchName>
        <reportCode>PM001</reportCode>
        <reportName>Example Report for Stocks</reportName>
        <memberId>XXETF</memberId>
        <memberName>Xetra ETF</memberName>
        <reportDate>2022-01-02</reportDate>
    </reportHeader>
    <pm001Grp>
        <pm001Grp1>
            <pm001KeyGrp1>
                <unitGrp>
                    <unitCode>ABC01</unitCode>
                    <lngName>ABC Company 01</lngName>
                    <unitId>10000</unitId>
                </unitGrp>
            </pm001KeyGrp1>
            <pm001Grp2>
                <pm001KeyGrp2>
                    <instrumentGrp1>
                        <product>Stock1</product>
                        <instType>1</instType>
                        <instId>250000</instId>
                        <instShortcode>ELL01</instShortcode>
                        <instNam>Example Stock 1</instNam>
                        <currencyCode>EUR</currencyCode>
                    </instrumentGrp1>
                </pm001KeyGrp2>
                <pm001Rec>
                    <tradingDate>2022-01-01</tradingDate>
                    <numTradesDay>8</numTradesDay>
                    <rateVola>100</rateVola>
                </pm001Rec>
            </pm001Grp2>
            <pm001Grp2>
                <pm001KeyGrp2>
                    <instrumentGrp1>
                        <product>Stock2</product>
                        <instType>1</instType>
                        <instId>251400</instId>
                        <instShortcode>ELL02</instShortcode>
                        <instNam>Example Stock 2</instNam>
                        <currencyCode>EUR</currencyCode>
                    </instrumentGrp1>
                </pm001KeyGrp2>
                <pm001Rec>
                    <tradingDate>2022-01-01</tradingDate>
                    <numTradesDay>2</numTradesDay>
                    <rateVola>90</rateVola>
                </pm001Rec>
            </pm001Grp2>
            <pm001Grp2>
                <pm001KeyGrp2>
                    <instrumentGrp1>
                        <product>Stock3</product>
                        <instType>1</instType>
                        <instId>260000</instId>
                        <instShortcode>ELL03</instShortcode>
                        <instNam>Example Stock 3</instNam>
                        <currencyCode>EUR</currencyCode>
                    </instrumentGrp1>
                </pm001KeyGrp2>
                <pm001Rec>
                    <tradingDate>2022-01-01</tradingDate>
                    <numTradesDay>6</numTradesDay>
                    <rateVola>0</rateVola>
                </pm001Rec>
            </pm001Grp2>
        </pm001Grp1>
    </pm001Grp>
    <pm001Grp>
        <pm001Grp1>
            <pm001KeyGrp1>
                <unitGrp>
                    <unitCode>DEF02</unitCode>
                    <lngName>DEF Company 02</lngName>
                    <unitId>11000</unitId>
                </unitGrp>
            </pm001KeyGrp1>
            <pm001Grp2>
                <pm001KeyGrp2>
                    <instrumentGrp1>
                        <product>Stock1</product>
                        <instType>1</instType>
                        <instId>250000</instId>
                        <instShortcode>ELL01</instShortcode>
                        <instNam>Example Stock 1</instNam>
                        <currencyCode>EUR</currencyCode>
                    </instrumentGrp1>
                </pm001KeyGrp2>
                <pm001Rec>
                    <tradingDate>2022-01-01</tradingDate>
                    <numTradesDay>3</numTradesDay>
                    <rateVola>1</rateVola>
                </pm001Rec>
            </pm001Grp2>
            <pm001Grp2>
                <pm001KeyGrp2>
                    <instrumentGrp1>
                        <product>Stock2</product>
                        <instType>1</instType>
                        <instId>251400</instId>
                        <instShortcode>ELL02</instShortcode>
                        <instNam>Example Stock 2</instNam>
                        <currencyCode>EUR</currencyCode>
                    </instrumentGrp1>
                </pm001KeyGrp2>
                <pm001Rec>
                    <tradingDate>2022-01-01</tradingDate>
                    <numTradesDay>35</numTradesDay>
                    <rateVola>45</rateVola>
                </pm001Rec>
            </pm001Grp2>
            <pm001Grp2>
                <pm001KeyGrp2>
                    <instrumentGrp1>
                        <product>Stock3</product>
                        <instType>1</instType>
                        <instId>260000</instId>
                        <instShortcode>ELL03</instShortcode>
                        <instNam>Example Stock 3</instNam>
                        <currencyCode>EUR</currencyCode>
                    </instrumentGrp1>
                </pm001KeyGrp2>
                <pm001Rec>
                    <tradingDate>2022-01-01</tradingDate>
                    <numTradesDay>34</numTradesDay>
                    <rateVola>60</rateVola>
                </pm001Rec>
            </pm001Grp2>
        </pm001Grp1>
    </pm001Grp>
</pm001>

The data need to be in a table in my database on sql server:

exchName VARCHAR(10) NOT NULL,
reportCode VARCHAR(5) NOT NULL,
reportName VARCHAR(MAX) NOT NULL,
memberId VARCHAR(5) NOT NULL,
memberName VARCHAR(MAX) NOT NULL,
reportDate DATETIME NOT NULL,
unitCode VARCHAR(5) NOT NULL, 
lngName VARCHAR(MAX) NOT NULL,
unitId INT NOT NULL,
product VARCHAR(MAX) NOT NULL,
instType TINYINT NOT NULL,
instId INT NOT NULL,
instShortcode VARCHAR(5) NOT NULL,
instNam VARCHAR(MAX) NOT NULL,
currencyCode CHAR(3) NOT NULL,
tradingDate DATETIME NOT NULL,
numTradesDay INT NOT NULL,
rateVola INT NOT NULL

I have to have a connection between this nodes:

XML nodes:

'pm001/reportHeader'
'pm001/pm001Grp/pm001Grp1/pm001KeyGrp1/unitGrp'
'pm001/pm001Grp/pm001Grp1/pm001Grp2/pm001KeyGrp2/instrumentGrp1'
'pm001/pm001Grp/pm001Grp1/pm001Grp2/pm001Rec'

Do anyone have an idea how to handle this?

score 1 · Accepted Answer · answered Feb 04 '22 at 15:39

I would advise you not to use the old OPENXML function. It is deprecated and has many issues.

Instead you can use the newer XQuery .nodes and .value functions.

You just need to pick which nodes to shred, and pass through one to the next using CROSS APPLY. Just add the INSERT at the top to actually insert into the table

SELECT
  x2.reportHeader.value('(exchName/text())[1]','varchar(10)'),
  x2.reportHeader.value('(reportCode/text())[1]','varchar(5)'),
  x2.reportHeader.value('(reportName/text())[1]','varchar(max)'),
  x2.reportHeader.value('(memberId/text())[1]','varchar(5)'),
  x2.reportHeader.value('(memberName/text())[1]','varchar(max)'),
  x2.reportHeader.value('(reportDate/text())[1]','date'),
  x4.unitGrp.value('(unitCode/text())[1]','varchar(5)'),
  x4.unitGrp.value('(lngName/text())[1]','varchar(max)'),
  x4.unitGrp.value('(unitId/text())[1]','int'),
  x5.pmGrp2.value('(pm001KeyGrp2/instrumentGrp1/product/text())[1]','varchar(max)'),
  x5.pmGrp2.value('(pm001KeyGrp2/instrumentGrp1/instType/text())[1]','tinyint'),
  x5.pmGrp2.value('(pm001KeyGrp2/instrumentGrp1/instId/text())[1]','int'),
  x5.pmGrp2.value('(pm001KeyGrp2/instrumentGrp1/instShortcode/text())[1]','varchar(5)'),
  x5.pmGrp2.value('(pm001KeyGrp2/instrumentGrp1/instNam/text())[1]','varchar(max)'),
  x5.pmGrp2.value('(pm001KeyGrp2/instrumentGrp1/currencyCode/text())[1]','char(3)'),
  x5.pmGrp2.value('(pm001Rec/tradingDate/text())[1]','date'),
  x5.pmGrp2.value('(pm001Rec/numTradesDay/text())[1]','int'),
  x5.pmGrp2.value('(pm001Rec/rateVola/text())[1]','int')
FROM @xml.nodes('pm001') x1(pm001)
CROSS APPLY x1.pm001.nodes('reportHeader') x2(reportHeader)
CROSS APPLY x1.pm001.nodes('pm001Grp/pm001Grp1') x3(pmGrp)
CROSS APPLY x3.pmGrp.nodes('pm001KeyGrp1/unitGrp') x4(unitGrp)
CROSS APPLY x3.pmGrp.nodes('pm001Grp2') x5(pmGrp2);

db<>fiddle

What issues with OPENXML do you have in mind? Is there anything official? — wqw, Feb 04 '22 at 22:45
It can be subject to memory leaks unless you are careful with `BEGIN TRY` `BEGIN CATCH`, it also doesn't deal well with namespaces — Charlieface, Feb 05 '22 at 18:53

Import nested XML file to SQL Server

1 Answers1