XML to Columns

Question

Below is the XML that i need to covert into columns please help , XML is coming from column name called "DESC" table name is "rawXML"

Columns: USER id,US_USERID,US_PASSWORD,US_SHORT,FIRST,LAST,US_LAST_PASSWORD_UPDATE

<USER id="05100">

 <US_USERID>YU</US_USERID>

  <US_PASSWORD>4026531934</US_PASSWORD>

  <US_SHORT>yu</US_SHORT>

  <US_XPN>

    <FIRST>Yehuda</FIRST>

    <LAST>Unger</LAST>

  </US_XPN>

  <US_LAST_PASSWORD_UPDATE>2006-01-19T16:10</US_LAST_PASSWORD_UPDATE>
</USER>

---

ramakrishna

Answer 1

Columns: USER id,US_USERID,US_PASSWORD,US_SHORT,FIRST,LAST,US_LAST_PASSWORD_UPDATE
declare @rawXML table (DESC xml);
insert @rawXML values(
'<USER id="05100">
 <US_USERID>YU</US_USERID>
  <US_PASSWORD>4026531934</US_PASSWORD>
  <US_SHORT>yu</US_SHORT>
  <US_XPN>
    <FIRST>Yehuda</FIRST>
    <LAST>Unger</LAST>
  </US_XPN>
  <US_LAST_PASSWORD_UPDATE>2006-01-19T16:10</US_LAST_PASSWORD_UPDATE>
</USER>');
select 
x.user.value ('(@id)[1]', 'varchar(20)') as id
, x.user.value ('(US_USERID)[1]', 'varchar(20)') as US_USERID
, x.user.value ('(US_PASSWORD)[1]', 'varchar(20)') as US_PASSWORD
, x.user.value ('(US_SHORT)[1]', 'varchar(20)') as US_SHORT
, x.user.value ('(US_XPN/FIRST)[1]', 'varchar(20)') as [FIRST]
, x.user.value ('(US_XPN/LAST)[1]', 'varchar(20)') as [LAST]
, x.user.value ('(US_LAST_PASSWORD)[1]', 'varchar(20)') as US_LAST_PASSWORD
from @rawXML r
cross apply r.DESC.nodes('USER') as x(user)

---

Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

Check this:

declare @xml xml
set @xml = '<USER id="05100">

 <US_USERID>YU</US_USERID>

  <US_PASSWORD>4026531934</US_PASSWORD>

  <US_SHORT>yu</US_SHORT>

  <US_XPN>

    <FIRST>Yehuda</FIRST>

    <LAST>Unger</LAST>

  </US_XPN>

  <US_LAST_PASSWORD_UPDATE>2006-01-19T16:10</US_LAST_PASSWORD_UPDATE>
</USER>'

select 
	t.c.value('../@id[1]', 'varchar(10)') as [USER],
	t.c.value('../US_USERID[1]', 'varchar(10)') as [US_USERID],
	t.c.value('../US_PASSWORD[1]', 'varchar(10)') as [US_PASSWORD],
	t.c.value('../US_SHORT[1]', 'varchar(10)') as [US_SHORT],
	t.c.value('./FIRST[1]', 'varchar(10)') as [FIRST],
	t.c.value('./LAST[1]', 'varchar(10)') as [LAST],
	t.c.value('../US_LAST_PASSWORD_UPDATE[1]', 'varchar(10)') as [US_LAST_PASSWORD_UPDATE]
from @xml.nodes('//USER/US_XPN') as t(c)

---

~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page