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)
);
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
