I can not collect one ms sql table from the xml hierarchy

Question:

Есть file.XML:

<?xml version="1.0" encoding="windows-1251"?>
<ZL_LIST>
    <ZGLV>
        <VERSION>2.1</VERSION>
        <DATA>2016-03-09</DATA>
        <FILENAME>file</FILENAME>
    </ZGLV>
    <SCHET>
        <YEAR>2016</YEAR>
        <MONTH>01</MONTH>
    </SCHET>
    <ZAP>
        <N_ZAP>20199</N_ZAP>
        <PR_NOV>0</PR_NOV>
        <PERSON>
            <ID>2801201658079965126211466287927</ID>
        </PERSON>
        <SLUCH>
            <EXTR>22</EXTR>
            <PROFIL>112</PROFIL>
            <NHISTORY>169</NHISTORY>
            <P_OTK>0</P_OTK>
            <DATE_1>2016-01-15</DATE_1>
            <DATE_2>2016-01-21</DATE_2>
        </SLUCH>
    </ZAP>    
</ZL_LIST>

The ZAP structure is repeated several times in one file.

You need to collect a table of the form:

DATA | YEAR | MONTH | ID | EXTR | PROFIL

I do this:

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'file.xml', SINGLE_BLOB) AS x

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

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT  EXTR, PROFIL
FROM OPENXML(@hDoc, 'ZL_LIST/ZAP/SLUCH')
WITH 
(  EXTR [varchar](100) 'EXTR',
  PROFIL [varchar](100) 'PROFIL'  )

EXEC sp_xml_removedocument @hDoc

I get the values ​​of tags, but I can not get the values ​​of the upstream tags to them (DATA | YEAR | MONTH | ID). Please help me figure it out?

Answer:

You can go to higher-level nodes in the hierarchy using .. , you can go to ZGLV and SCHET from the root element (eg /ZL_LIST/ZGLV ), because them one at a time in the file.

This is how it should work:

SELECT DATA, YEAR, MONTH, ID, EXTR, PROFIL
FROM OPENXML(@hDoc, 'ZL_LIST/ZAP/SLUCH')
WITH
(
    DATA date '(/ZL_LIST/ZGLV/DATA)[1]',
    [YEAR] int '(/ZL_LIST/SCHET/YEAR)[1]',
    [MONTH] int '(/ZL_LIST/SCHET/MONTH)[1]',
    [ID] varchar(100) '../PERSON[1]/ID[1]',
    EXTR [varchar](100) 'EXTR',
    PROFIL [varchar](100) 'PROFIL'
)

The same with XQuery:

SELECT
    DATA = list.c.value('(ZGLV/DATA/text())[1]', 'date'),
    YEAR = list.c.value('(SCHET/YEAR/text())[1]', 'int'),
    MONTH = list.c.value('(SCHET/MONTH/text())[1]', 'int'),
    ID = zap.c.value('(PERSON/ID/text())[1]', 'varchar(100)'),
    EXTR = zap.c.value('(SLUCH/EXTR/text())[1]', 'varchar(100)'),
    PROFIL = zap.c.value('(SLUCH/PROFIL/text())[1]', 'varchar(100)')
FROM @XML.nodes('/ZL_LIST[1]') list(c)
    CROSS APPLY list.c.nodes('ZAP') zap(c)
Scroll to Top