Answered by:
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
Friday, July 12, 2013 11:31 AM
Answers
-
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
- Marked as answer by jakkampudi Friday, July 12, 2013 3:19 PM
Friday, July 12, 2013 12:11 PM -
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- Marked as answer by jakkampudi Friday, July 12, 2013 3:19 PM
Friday, July 12, 2013 12:15 PM
All replies
-
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
- Marked as answer by jakkampudi Friday, July 12, 2013 3:19 PM
Friday, July 12, 2013 12:11 PM -
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- Marked as answer by jakkampudi Friday, July 12, 2013 3:19 PM
Friday, July 12, 2013 12:15 PM