Retrieving XML Data from the database table in sql server

 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;

 See the result. 

Get the expected result.

Happy Learning! J