none
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