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

ZAP structure is repeated several times in one file.

We 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 tag values, but I can't get the values ​​of the parent tags for them (DATA | YEAR | MONTH | ID). Help, please, sort it out?

  • "ZAP repeats several times" , and ZGLV and SCHET - one per file? - i-one
  • Yes, one per file and ZGLV and SCHET - Dmitriy

1 answer 1

You can go to the higher nodes in the hierarchy using .. , you can go to ZGLV and SCHET from the root element (eg /ZL_LIST/ZGLV ), because them one by one 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)
  • I did something like this, but I couldn’t think of it. only with this option does not get the ID, as the ZAP tag is repeated. Why do you put [1]? - Dmitry
  • Thank you very much, everything turned out .. they rule) - Dmitry
  • @ Dmitriy, [1] for XQuery variant in some places is necessary (method .value() ), in others (eg .nodes('/ZL_LIST[1]') ) - this is just optimization (to parse faster). For OPENXML yes, it is not necessary to set [1] (and maybe it doesn’t even affect the speed of parsing - I didn’t check it), but for reliability / unambiguity I would put it. - i-one