Advertisements
Home > SQL Server Questions, XML > Convert XML to Columns – MSDN TSQL forum

Convert XML to Columns – MSDN TSQL forum


–> 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>

 

–>My Answer:

Check the query below:

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)

 

Ref Link.


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: