convert xml variable data as a table

• DURGESH-769360

  SSC Eights!

  Points: 968

  More actions

  December 13, 2010 at 9:38 am

  #232081

  Hi all,

  How to convert a xml file to a table

  DECLARE @xml AS XML

  set @xml= N'

  <books>

  <category type="ssrs">

  <book lang="en">Microsoft SQL Server 2008 Reporting Services Step by Step</book>

  <book1 lang="en">Microsoft SQL Server 2008 Reporting Services</book1>

  <book2 lang="en">Applied Microsoft SQL Server 2008 Reporting Services</book2>

  <book3 lang="en">Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports</book3>

  </category>

  <category type="ssas">

  <book lang="en">Microsoft SQL Server 2008 Analysis Services Step by Step</book>

  <book1 lang="en">Microsoft SQL Server 2008 MDX Step by Step</book1>

  <book2 lang="en">Expert Cube Development with Microsoft SQL Server 2008 Analysis Services</book2>

  </category>

  <category type="bi">

  <book lang="en">Smart Business Intelligence Solutions with Microsoft SQL Server 2008</book>

  <book1 lang="en">Delivering Business Intelligence with Microsoft SQL Server 2008</book1>

  </category>

  </books>

  '

  SELECT

  [Book Name].value('.','varchar(100)') AS Book,

  [Book Name].value('.','varchar(100)') AS Book1,

  [Book Name].value('@lang','varchar(100)') AS [Language],

  [Book Name].value('../@type','varchar(100)') AS Category

  FROM @xml.nodes('/books/category/book') Books([Book Name])

• VSSGeorge

  SSCrazy Eights

  Points: 8151

  More actions

  December 13, 2010 at 11:19 pm

  #1262041

  Hi

  This link may be useful to you

  http://zielynsqlblog.com/2010/02/10/convert-an-xml-variable-datatype-to-relational-table-sql-server/