Transform XML data into Tabular format in T-SQL

Question

I would like to know how I could transform XML hierarchical data into tabular format and join with other relational database tables in SQL Server 2005 T-SQL (XPath/XQuery).

For instance,

<Employees>
  <Employee ID="001" Name="David" />
  <Employee ID="002" Name="Mike" />
  <Employee ID="003" Name="Alex" />
  <Employee ID="004" Name="Morris" />
</Employees>

To ..

 ID       Name
  --------+--------
  001       David
  002       Mike
  003       Alex
  004       Morris

Thanks for your suggestion. :)

Joe Stefanelli · Accepted Answer · 2010-12-23 14:51:09Z

4

Here's one way:

declare @x xml

set @x = '<Employees>
              <Employee ID="001" Name="David" />
              <Employee ID="002" Name="Mike" />
              <Employee ID="003" Name="Alex" />
              <Employee ID="004" Name="Morris" />
          </Employees>'

select emp.e.value('@ID','varchar(10)') as ID, 
       emp.e.value('@Name','varchar(10)') as Name
    from @x.nodes('Employees/Employee') as emp(e)

answered Dec 23, 2010 at 14:51

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Demitrius Nelon · Accepted Answer · 2010-12-23 15:06:39Z

1

Here is a slight variation on the answer Joe submitted earlier:

DECLARE @X xml
SET @X = '<Employees>
   <Employee ID="001" Name="David" />
   <Employee ID="002" Name="Mike" />
   <Employee ID="003" Name="Alex" />
   <Employee ID="004" Name="Morris" />
 </Employees>'

 SELECT
 [Employee].value('@ID','int')As ID,
 [Employee].value('@Name','varchar(10)') As Name
 FROM
 @x.nodes('/Employees/Employee') Employee([Employee])

This was done in MSSQL Server 2008 R2

Reference Site

answered Dec 23, 2010 at 15:06

Demitrius Nelon

1,2681 gold badge11 silver badges30 bronze badges

Comments

wizzardz · Accepted Answer · 2010-12-23 15:42:14Z

0

I hopes this one helps you

 declare @xml varchar(max)

 SET @xml = '<Employees>
  <Employee ID="001" Name="David" />
  <Employee ID="002" Name="Mike" />
  <Employee ID="003" Name="Alex" />
  <Employee ID="004" Name="Morris" />
</Employees>'

 Declare @documentHandler INT 

 EXEC sp_xml_preparedocument @documentHandler OUTPUT,@xml

 SELECT * 
 FROM OPENXML(@documentHandler,'/Employees/Employee')
       WITH (ID varchar(20),
             Name varchar(150))


 EXEC sp_xml_removedocument @documentHandler

answered Dec 23, 2010 at 15:42

wizzardz

5,9125 gold badges46 silver badges67 bronze badges

3 Comments

Joe Stefanelli Over a year ago

This older method is less efficient and generally performs worse than the technique given in the other two answers.

Joe Stefanelli Over a year ago

Yes. I've seen several cases where I've been able to improve performance by replacing your method with the newer one. It gets even worse when you run into a situation where a developer has forgotten the sp_xml_removedocument.

wizzardz Over a year ago

@Joe: thanks Joe,I was using this sp im most palaces,I think its better chance them now. +1 for your answer :)

Collectives™ on Stack Overflow

Transform XML data into Tabular format in T-SQL

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related