Storing the XML data into Rows and Columns in the table

 In the previous post we see how to store the XML file data in the sql server table.

Read here: Storing the XML file into the table

https://bageshkumarbagi-msbi.blogspot.com/2021/07/storing-xml-file-into-table.html

Suppose we want to Shredder the xml file and retrieve the xml file into the rows and column and storing this records into the table.

See the below file.  

It is having the below nodes. These nodes will be the column of the table.

·         id

·         author

·         title

·         genre

·         price

·         publish_date

·         description

Below is the table to store these data.

CREATE TABLE book(                 id varchar(100) NULL,                 author varchar(100) NULL,                 title varchar(100) NULL,                 genre varchar(100) NULL,                 price varchar(100) NULL,                 publish_date varchar(100) NULL,                 description varchar(100) NULL )

 We can Shredder into the below

§  Using sp_xml_preparedocument and sp_xml_removedocument sp

§  Using node function

Using sp_xml_preparedocument and sp_xml_removedocument sp

sp_xml_preparedocument  Reads

The XML text provided as input, parses the text by using the MSXML parser (Msxmlsql.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes in the XML document: elements, attributes, text, comments, and so on. It returns a handle that can be used to access the newly created internal representation of the XML document. This handle is valid for the duration of the session.

sp_xml_removedocument :

Removes the internal representation of the XML document specified by the document handle and invalidates the document handle.

Below is the script to Shredder the XML data and load these data into the table.

DECLARE @DocID INT; DECLARE @DocXML VARCHAR(MAX); SELECT @DocXML = CAST(XMLSource AS VARCHAR(MAX)) FROM OPENROWSET(BULK 'H:\SSIS1\Source\XML_Files\Computer.Xml', SINGLE_BLOB) AS X (XMLSource); EXECUTE master.dbo.sp_xml_preparedocument @DocID OUTPUT, @DocXML;  insert into book(                    id                   ,author                   ,title                   ,genre                   ,price                   ,publish_date                   ,description ) SELECT id,author,title,genre,price,publish_date,description FROM OPENXML(@DocID, 'catalog/book', 2) WITH (  id VARCHAR(100) ,author VARCHAR(100) ,title VARCHAR(100) ,genre varchar(100) ,price varchar(100) ,publish_date varchar(100) ,description varchar(100) );  EXECUTE master.dbo.sp_xml_removedocument @DocID;

 Data inserted successfully.  

See the records in the table. 

Using Node function

Below is the script

DECLARE @XMLSource XML; SELECT @XMLSource = CAST(XMLSource AS XML) FROM OPENROWSET(BULK 'H:\SSIS1\Source\XML_Files\Computer.Xml', SINGLE_BLOB) AS X (XMLSource); insert into book(                    id                   ,author                   ,title                   ,genre                   ,price                   ,publish_date                   ,description ) SELECT SRC.Client.value('id[1]', 'varchar(100)') AS ID ,SRC.Client.value('author[1]', 'VARCHAR(100)') AS author ,SRC.Client.value('title[1]', 'VARCHAR(100)') AS title ,SRC.Client.value('genre[1]', 'VARCHAR(100)') AS genre ,SRC.Client.value('price[1]', 'VARCHAR(100)') AS price ,SRC.Client.value('publish_date[1]', 'VARCHAR(100)') AS publish_date ,SRC.Client.value('description[1]', 'VARCHAR(100)') AS description FROM @XMLSource.nodes('catalog/book') AS SRC (Client);

Before running the script  

Running this script.  

See the records into the table