niedziela, 28 maja 2017

[EN] SQL Server 2014 SP2 - Processing data from XML file using OPENXML

Microsoft SQL Server offers opportunity work with data in XML format. One of the T-SQL functions is OPENXML, which allows to navigate through the XML file structure to process the information it contains. The XML file may be loaded into the database using - for example - OPENROWSET. 

In this entry, I show how to import data from an XML file into a database and then display the content of the selected node of XML file.  For training purposes I will use the XML file provided by the National Health Fund, which contains information on blocked prescriptions numbers. This file is possible to download from website under the link "pobierz komunikat XML - rozwinięty".

(to see the full size image click on it)

The *.ARZ file will be downloaded. From this ARZ we can extract XML file.

This XML file contains a simple structure.

(to see the full size image click on it)

In order to import the XML file (/komunikat/blokada/zakres-nr/nr-recepty node)  into the database and display its contents as a list of blocked prescriptions numbers, we can use the following script.

DECLARE @idoc int;
SELECT @out=BulkColumn
OPENROWSET (BULK 'C:\xml\20170528_1557_rozwiniety.xml',SINGLE_BLOB) as import
EXEC sp_xml_preparedocument @idoc OUTPUT, @out;  
FROM  OPENXML (@idoc, '/komunikat/blokada/zakres-nr/nr-recepty',0) WITH (
numer varchar(255) 


(to see the full size image click on it)

For more information about OPENXML see offical Microsoft documentation of T-SQL: