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 http://recepty.nfz.gov.pl/ 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;
DECLARE @out XML;
SELECT @out=BulkColumn
FROM 
OPENROWSET (BULK 'C:\xml\20170528_1557_rozwiniety.xml',SINGLE_BLOB) as import
EXEC sp_xml_preparedocument @idoc OUTPUT, @out;  
SELECT *  
FROM  OPENXML (@idoc, '/komunikat/blokada/zakres-nr/nr-recepty',0) WITH (
numer varchar(255) 
);  

Effect

(to see the full size image click on it)

For more information about OPENXML see offical Microsoft documentation of T-SQL: https://docs.microsoft.com/en-us/sql/t-sql/functions/openxml-transact-sql

Brak komentarzy:

Prześlij komentarz