XML data we are generally storing it into XML data type and when we are using that data into the application we need to shedder the xml data into the rows and columns.
In this demo
we will see how to shedder the Xml data into the rows and columns.
Below is the source data.
With the
help of below sql script we will shedder the xml data into the rows and
columns.
|
DECLARE @XMLSource XML, @number_of_records
INT, @cnt INT,
@SearchKeyword VARCHAR(100); DECLARE @tblxmldata TABLE ( id INT IDENTITY(1, 1), searchkeyword VARCHAR(100), xmlsource XML ) DECLARE @tbldata TABLE ( searchkeyword VARCHAR(100), id VARCHAR(100), author VARCHAR(50), title VARCHAR(50), genre VARCHAR(50), price VARCHAR(50), publish_date VARCHAR(50), description VARCHAR(500) ) SET @cnt=1; INSERT INTO @tblxmldata (searchkeyword, xmlsource) SELECT searchkeyword, xmldata FROM
xml_file_details; SELECT @number_of_records = Count(*) FROM @tblxmldata; WHILE @cnt <= @number_of_records BEGIN SELECT @SearchKeyword = searchkeyword, @XMLSource = xmlsource FROM @tblxmldata WHERE id = @cnt; INSERT INTO @tbldata (searchkeyword, id, author, title, genre, price,
publish_date, description) SELECT @SearchKeyword AS SearchKeyword, src.client.value ('id[1]', 'varchar(100)') AS ID, src.client.value('author[1]', 'VARCHAR(50)') AS author, src.client.value('title[1]', 'VARCHAR(50)') AS title, src.client.value('genre[1]', 'VARCHAR(50)') AS genre, src.client.value('price[1]', 'VARCHAR(50)') AS price, src.client.value('publish_date[1]', 'VARCHAR(50)') AS publish_date, src.client.value('description[1]', 'VARCHAR(500)') AS description FROM @XMLSource.nodes('catalog/book') AS SRC (client); SET @cnt=@cnt + 1; END; SELECT * FROM @tbldata; |
Get the expected result.
Happy Learning! J
