none
Convert xml text in a cell to Columns RRS feed

  • Question

  • Hi All,

    Our front end development team saves surveys in a SQL database table as xml text. Each survey has  different set of fields and need help on how to automatically convert the cell holding the xml text to multiple columns.

    Below is an example of the xml text in a cell which needs to be split into individual columns.

    <?xml version="1.0" encoding="utf-16"?>  <root>    <userid>JS 30/08/1981</userid>    <column___1>0</column___1>    <column___2>1</column___2>    <column___3>0</column___3>    <column___4>0</column___4>    <column___5>0</column___5>    <column___6>0</column___6>    <column___7>0</column___7>    <column___8>0</column___8>    <column___9>0</column___9>    <column___1_0>0</column___1_0>    <column___1_1>0</column___1_1>    <column___1_2>0</column___1_2>    <column___1_3>0</column___1_3>    <column___1_4>0</column___1_4>    <column___1_5>0</column___1_5>    <column___1_6>0</column___1_6>    <column___1_7>0</column___1_7>    <column___1_8>0</column___1_8>    <column___1_9>0</column___1_9>    <column___2_0>0</column___2_0>    <column___2_1>0</column___2_1>    <column___D_S>0</column___D_S>    <column___A_S>2</column___A_S>    <column___S_S>0</column___S_S>    <userid___u_i_d>5</userid___u_i_d>  </root>

    Below is the desired output required

    column___1 column___2 column___3 column___4 column___5 column___6 column___7 column___8 column___9 column___10 column___11 column___12 column___13 column___14 column___15 column___16 column___17 column___18 column___19 column___20 column___21 column___D_S column___A_S column___S_S userid___u_i_d
    0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 5

    Thanks

    Jag



    • Edited by jaggy99 Thursday, January 3, 2019 4:15 AM
    Thursday, January 3, 2019 4:10 AM

All replies

  • Check this..

    declare @xml xml='<root>    <userid>JS 30/08/1981</userid>    <column___1>0</column___1>    <column___2>1</column___2>    <column___3>0</column___3>    <column___4>0</column___4>    <column___5>0</column___5>    <column___6>0</column___6>    <column___7>0</column___7>    <column___8>0</column___8>    <column___9>0</column___9>    <column___1_0>0</column___1_0>    <column___1_1>0</column___1_1>    <column___1_2>0</column___1_2>    <column___1_3>0</column___1_3>    <column___1_4>0</column___1_4>    <column___1_5>0</column___1_5>    <column___1_6>0</column___1_6>    <column___1_7>0</column___1_7>    <column___1_8>0</column___1_8>    <column___1_9>0</column___1_9>    <column___2_0>0</column___2_0>    <column___2_1>0</column___2_1>    <column___D_S>0</column___D_S>    <column___A_S>2</column___A_S>    <column___S_S>0</column___S_S>    <userid___u_i_d>5</userid___u_i_d>  </root>'

    SELECT cast(a.b.query('data(column___1)') as varchar(10)) AS column___1,
    cast(a.b.query('data(column___2)') as varchar(10)) AS column___2,
    cast(a.b.query('data(column___3)')  as varchar(10)) AS column___3,
    cast(a.b.query('data(column___4)')  as varchar(10)) AS column___4,
    cast(a.b.query('data(column___5)')  as varchar(10)) AS column___5,
    cast(a.b.query('data(column___6)')  as varchar(10)) AS column___6,
    cast(a.b.query('data(column___7)')  as varchar(10)) AS column___7,
    cast(a.b.query('data(column___8)')  as varchar(10)) AS column___8,
    cast(a.b.query('data(column___9)')  as varchar(10)) AS column___9,
    cast(a.b.query('data(column___1_0)')  as varchar(10)) AS column___10,
    cast(a.b.query('data(column___1_1)')  as varchar(10)) AS column___11,
    cast(a.b.query('data(column___1_2)')  as varchar(10)) AS column___12,
    cast(a.b.query('data(column___1_3)')  as varchar(10)) AS column___13,
    cast(a.b.query('data(column___1_4)')  as varchar(10)) AS column___14,
    cast(a.b.query('data(column___1_5)')  as varchar(10)) AS column___15,
    cast(a.b.query('data(column___1_6)')  as varchar(10)) AS column___16,
    cast(a.b.query('data(column___1_7)')  as varchar(10)) AS column___17,
    cast(a.b.query('data(column___1_8)')  as varchar(10)) AS column___18,
    cast(a.b.query('data(column___1_9)')  as varchar(10)) AS column___19,
    cast(a.b.query('data(column___2_0)')  as varchar(10)) AS column___20,
    cast(a.b.query('data(column___2_1)')  as varchar(10)) AS column___21,
    cast(a.b.query('data(column___D_S)')  as varchar(10)) AS column___D_S,
    cast(a.b.query('data(column___A_S)')  as varchar(10)) AS column___A_S,
    cast(a.b.query('data(column___S_S)')  as varchar(10)) AS column___S_S,
    cast(a.b.query('data(userid___u_i_d)')  as varchar(10)) AS userid___u_i_d
    FROM    @xml.nodes('*') a(b)
    • Edited by SQLNeophyte Thursday, January 3, 2019 5:05 AM
    Thursday, January 3, 2019 4:42 AM
  • 1) You can use a query like below,

    create table test123(sno xml)
    declare @i xml = convert(xml,N'<?xml version="1.0" encoding="utf-16"?>  <root>    <userid>JS 30/08/1981</userid>    <column___1>0</column___1>    <column___2>1</column___2>    <column___3>0</column___3>    <column___4>0</column___4>    <column___5>0</column___5>    <column___6>0</column___6>    <column___7>0</column___7>    <column___8>0</column___8>    <column___9>0</column___9>    <column___1_0>0</column___1_0>    <column___1_1>0</column___1_1>    <column___1_2>0</column___1_2>    <column___1_3>0</column___1_3>    <column___1_4>0</column___1_4>    <column___1_5>0</column___1_5>    <column___1_6>0</column___1_6>    <column___1_7>0</column___1_7>    <column___1_8>0</column___1_8>    <column___1_9>0</column___1_9>    <column___2_0>0</column___2_0>    <column___2_1>0</column___2_1>    <column___D_S>0</column___D_S>    <column___A_S>2</column___A_S>    <column___S_S>0</column___S_S>    <userid___u_i_d>5</userid___u_i_d>  </root>')
    insert into [dbo].test123 values (@i)

    select 
    sno.value('(/root/column___1)[1]','int') AS column___1,
    sno.value('(/root/column___2)[1]','int') AS column___2,
    sno.value('(/root/column___3)[1]','int') AS column___3,
    sno.value('(/root/column___4)[1]','int') AS column___4,
    sno.value('(/root/column___5)[1]','int') AS column___5,
    sno.value('(/root/column___6)[1]','int') AS column___6,
    sno.value('(/root/column___7)[1]','int') AS column___7,
    sno.value('(/root/column___8)[1]','int') AS column___8,
    sno.value('(/root/column___9)[1]','int') AS column___9,
    sno.value('(/root/column___1_0)[1]','int') AS column___1_0,
    sno.value('(/root/column___1_1)[1]','int') AS column___1_1,
    sno.value('(/root/column___1_2)[1]','int') AS column___1_2,
    sno.value('(/root/column___1_3)[1]','int') AS column___1_3,
    sno.value('(/root/column___1_4)[1]','int') AS column___1_4,
    sno.value('(/root/column___1_5)[1]','int') AS column___1_5,
    sno.value('(/root/column___1_6)[1]','int') AS column___1_6,
    sno.value('(/root/column___1_7)[1]','int') AS column___1_7,
    sno.value('(/root/column___1_8)[1]','int') AS column___1_8,
    sno.value('(/root/column___1_9)[1]','int') AS column___1_9,
    sno.value('(/root/column___2_0)[1]','int') AS column___2_0,
    sno.value('(/root/column___2_1)[1]','int') AS column___2_1,
    sno.value('(/root/column___D_S)[1]','int') AS column___D_S,
    sno.value('(/root/column___A_S)[1]','int') AS column___A_S,
    sno.value('(/root/column___S_S)[1]','int') AS column___S_S,
    sno.value('(/root/userid___u_i_d)[1]','int') AS userid___u_i_d
    from [dbo].test123 

    2) Use scalar promotion to create computed columns on the table depending on what columns you need. Check this article for more details. Below is for creating a computed column called column___1 and then just move the required data from xml column directly.

    CREATE FUNCTION [dbo].[udtest123_C1](@COL2 XML)
    RETURNS int
    WITH SCHEMABINDING
    BEGIN
    RETURN ISNULL(@COL2.value('(/root/column___1)[1]',  'int'), CHAR(1))
    END
    GO
     
    ALTER TABLE dbo.test123 ADD column___1  AS ([dbo].[udtest123_C1]([sno])) PERSISTED
    GO

    select * from test123


    Regards;
    Vivek Janakiraman





    Thursday, January 3, 2019 5:56 AM
  • you can use a logic like below

    declare @t table
    (
    x nvarchar(max)
    )
    insert @t
    values
    ('<?xml version="1.0" encoding="UTF-16"?><root><userid>JS30/08/1981</userid><column___1>0</column___1><column___2>1</column___2><column___3>0</column___3><column___4>0</column___4><column___5>0</column___5><column___6>0</column___6><column___7>0</column___7><column___8>0</column___8><column___9>0</column___9><column___1_0>0</column___1_0><column___1_1>0</column___1_1><column___1_2>0</column___1_2><column___1_3>0</column___1_3><column___1_4>0</column___1_4><column___1_5>0</column___1_5><column___1_6>0</column___1_6><column___1_7>0</column___1_7><column___1_8>0</column___1_8><column___1_9>0</column___1_9><column___2_0>0</column___2_0><column___2_1>0</column___2_1><column___D_S>0</column___D_S><column___A_S>2</column___A_S><column___S_S>0</column___S_S><userid___u_i_d>5</userid___u_i_d></root>')
    
    select n.value('userid[1]','varchar(20)') AS Userid,
    n.value('column___1[1]','int') AS column___1,
    n.value('column___2[1]','int') AS column___2,
    n.value('column___3[1]','int') AS column___3,
    n.value('column___4[1]','int') AS column___4,
    n.value('column___5[1]','int') AS column___5,
    n.value('column___6[1]','int') AS column___6,
    n.value('column___7[1]','int') AS column___7,
    n.value('column___8[1]','int') AS column___8,
    n.value('column___9[1]','int') AS column___9,
    n.value('column___1_0[1]','int') AS column___1_0,
    n.value('column___1_1[1]','int') AS column___1_1,
    n.value('column___1_2[1]','int') AS column___1_2,
    n.value('column___1_3[1]','int') AS column___1_3,
    n.value('column___1_4[1]','int') AS column___1_4,
    n.value('column___1_5[1]','int') AS column___1_5,
    n.value('column___1_6[1]','int') AS column___1_6,
    n.value('column___1_7[1]','int') AS column___1_7,
    n.value('column___1_8[1]','int') AS column___1_8,
    n.value('column___1_9[1]','int') AS column___1_9,
    n.value('column___2_0[1]','int') AS column___2_0,
    n.value('column___2_1[1]','int') AS column___2_1,
    n.value('column___D_S[1]','int') AS column___D_S,
    n.value('column___A_S[1]','int') AS column___A_S,
    n.value('column___S_S[1]','int') AS column___S_S,
    n.value('userid___u_i_d[1]','int') AS userid___u_i_d
    from
    (
    select cast(x as xml) as x
    from @t
    )t
    cross apply x.nodes('/root')m(n)
    
    
    /*
    Output
    ----------------------------------------------------------
    Userid	column___1	column___2	column___3	column___4	column___5	column___6	column___7	column___8	column___9	column___1_0	column___1_1	column___1_2	column___1_3	column___1_4	column___1_5	column___1_6	column___1_7	column___1_8	column___1_9	column___2_0	column___2_1	column___D_S	column___A_S	column___S_S	userid___u_i_d
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    JS30/08/1981	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	0	5
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 3, 2019 6:06 AM
  • Hi Neo,

    Thanks for the response. But this will only work if all the surveys have the same fields names. We usually have 50 surveys per project which will have 20-30 fields per surveys with different fieldname.

    Is there a way to write the query to automatically select the fields in < > and convert to a column.

    Hope I have explained the requirements and if there is any doubt please let me know.

    Thanks

    Jag

    Thursday, January 3, 2019 8:37 AM
  • Hi Neo,

    Thanks for the response. But this will only work if all the surveys have the same fields names. We usually have 50 surveys per project which will have 20-30 fields per surveys with different fieldname.

    Is there a way to write the query to automatically select the fields in < > and convert to a column.

    Hope I have explained the requirements and if there is any doubt please let me know.

    Thanks

    Jag

    Yes

    Please see

    https://visakhm.blogspot.com/2012/10/shred-data-as-well-as-metadata-from-xml.html

    So in your case you can do like this

    declare @t table
    (
    x nvarchar(max)
    )
    insert @t
    values
    ('<?xml version="1.0" encoding="UTF-16"?><root><userid>JS30/08/1981</userid><column___1>0</column___1><column___2>1</column___2><column___3>0</column___3><column___4>0</column___4><column___5>0</column___5><column___6>0</column___6><column___7>0</column___7><column___8>0</column___8><column___9>0</column___9><column___1_0>0</column___1_0><column___1_1>0</column___1_1><column___1_2>0</column___1_2><column___1_3>0</column___1_3><column___1_4>0</column___1_4><column___1_5>0</column___1_5><column___1_6>0</column___1_6><column___1_7>0</column___1_7><column___1_8>0</column___1_8><column___1_9>0</column___1_9><column___2_0>0</column___2_0><column___2_1>0</column___2_1><column___D_S>0</column___D_S><column___A_S>2</column___A_S><column___S_S>0</column___S_S><userid___u_i_d>5</userid___u_i_d></root>')
    
    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
    
    select n.value('local-name(.)','varchar(100)') AS ColumnName,n.value('.','varchar(100)') AS ColumnValue
    INTO #Temp
    from
    (
    select cast(x as xml) as x
    from @t
    )t
    cross apply x.nodes('/root/*')m(n)
    
    DECLARE @Cols varchar(max) = STUFF((SELECT ',[' + ColumnName + ']' FROM #Temp FOR XML PATH('')),1,1,'')
    DECLARE @SQL varchar(max) ='SELECT *
    FROM #Temp
    PIVOT(MAX(ColumnValue) FOR ColumnName IN (' + @Cols + '))p'
    
    EXEC(@SQL)
    
    
    /*
    Output
    ----------------------------------------------------------
    userid	column___1	column___2	column___3	column___4	column___5	column___6	column___7	column___8	column___9	column___1_0	column___1_1	column___1_2	column___1_3	column___1_4	column___1_5	column___1_6	column___1_7	column___1_8	column___1_9	column___2_0	column___2_1	column___D_S	column___A_S	column___S_S	userid___u_i_d
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    JS30/08/1981	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	0	5
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 3, 2019 9:03 AM
  • Hi Neo,

    Thanks for the response. But this will only work if all the surveys have the same fields names. We usually have 50 surveys per project which will have 20-30 fields per surveys with different fieldname.

    Is there a way to write the query to automatically select the fields in < > and convert to a column.

    Hope I have explained the requirements and if there is any doubt please let me know.

    Thanks

    Jag

    If the field names are dynamic, try below method:

    DECLARE @input XML =  '<root>    <userid>JS 30/08/1981</userid>    <column___1>0</column___1>    <column___2>1</column___2>    <column___3>0</column___3>    <column___4>0</column___4>    <column___5>0</column___5>    <column___6>0</column___6>    <column___7>0</column___7>    <column___8>0</column___8>    <column___9>0</column___9>    <column___1_0>0</column___1_0>    <column___1_1>0</column___1_1>    <column___1_2>0</column___1_2>    <column___1_3>0</column___1_3>    <column___1_4>0</column___1_4>    <column___1_5>0</column___1_5>    <column___1_6>0</column___1_6>    <column___1_7>0</column___1_7>    <column___1_8>0</column___1_8>    <column___1_9>0</column___1_9>    <column___2_0>0</column___2_0>    <column___2_1>0</column___2_1>    <column___D_S>0</column___D_S>    <column___A_S>2</column___A_S>    <column___S_S>0</column___S_S>    <userid___u_i_d>5</userid___u_i_d>  </root>'
    
    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)
    
    ;with cte as(
    
    SELECT
        NodeName = C.value('local-name(.)', 'varchar(50)'),
        NodeValue = C.value('(.)[1]', 'varchar(50)') 
    FROM @input.nodes('/root/*') AS T(C)
    )
    select * into ##temp from cte
    
    select @cols = STUFF((SELECT ',' + QUOTENAME(NodeName) 
                        from ##temp
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = N'SELECT ' + @cols + N' from 
                 (
                    select NodeName, NodeValue
                    from ##temp
                ) x
                pivot 
                (
                    max(NodeValue)
                    for NodeName in (' + @cols + N')
                ) p '
    
    exec sp_executesql @query;
    Thursday, January 3, 2019 12:34 PM
  • That worked but when i added another row, I get the error below

    Msg 213, Level 16, State 1, Line 5
    Column name or number of supplied values does not match table definition.

    Below is sample data in the 2nd row. 

    insert @t
    values
    ('<?xml version="1.0" encoding="UTF-16"?><root><userid>JS30/08/1981</userid><column___1>0</column___1><column___2>1</column___2><column___3>0</column___3><column___4>0</column___4><column___5>0</column___5><column___6>0</column___6><column___7>0</column___7><column___8>0</column___8><column___9>0</column___9><column___1_0>0</column___1_0><column___1_1>0</column___1_1><column___1_2>0</column___1_2><column___1_3>0</column___1_3><column___1_4>0</column___1_4><column___1_5>0</column___1_5><column___1_6>0</column___1_6><column___1_7>0</column___1_7><column___1_8>0</column___1_8><column___1_9>0</column___1_9><column___2_0>0</column___2_0><column___2_1>0</column___2_1><column___D_S>0</column___D_S><column___A_S>2</column___A_S><column___S_S>0</column___S_S><userid___u_i_d>5</userid___u_i_d></root>',
     '<?xml version="1.0" encoding="UTF-16"?><root><userid>KG12/10/1968</userid><column___1>0</column___1><column___2>0</column___2><column___3>0</column___3><column___4>0</column___4><column___5>0</column___5><column___6>0</column___6><column___7>0</column___7><column___8>0</column___8><column___9>0</column___9><column___1_0>0</column___1_0><column___1_1>0</column___1_1><column___1_2>0</column___1_2><column___1_3>0</column___1_3><column___1_4>0</column___1_4><column___1_5>0</column___1_5><column___1_6>0</column___1_6><column___1_7>0</column___1_7><column___1_8>0</column___1_8><column___1_9>0</column___1_9><column___2_0>0</column___2_0><column___2_1>0</column___2_1><column___D_S>0</column___D_S><column___A_S>0</column___A_S><column___S_S>0</column___S_S><userid___u_i_d>6</userid___u_i_d></root>' 	)

    Please note there are other columns in the table as shown below and we need to convert the xml code stored in the Information column as string to columns


    • Edited by jaggy99 Thursday, January 3, 2019 11:02 PM
    Thursday, January 3, 2019 11:01 PM
  • That worked but when i added another row, I get the error below

    Msg 213, Level 16, State 1, Line 5
    Column name or number of supplied values does not match table definition.

    Below is sample data in the 2nd row. 

    insert @t
    values
    ('<?xml version="1.0" encoding="UTF-16"?><root><userid>JS30/08/1981</userid><column___1>0</column___1><column___2>1</column___2><column___3>0</column___3><column___4>0</column___4><column___5>0</column___5><column___6>0</column___6><column___7>0</column___7><column___8>0</column___8><column___9>0</column___9><column___1_0>0</column___1_0><column___1_1>0</column___1_1><column___1_2>0</column___1_2><column___1_3>0</column___1_3><column___1_4>0</column___1_4><column___1_5>0</column___1_5><column___1_6>0</column___1_6><column___1_7>0</column___1_7><column___1_8>0</column___1_8><column___1_9>0</column___1_9><column___2_0>0</column___2_0><column___2_1>0</column___2_1><column___D_S>0</column___D_S><column___A_S>2</column___A_S><column___S_S>0</column___S_S><userid___u_i_d>5</userid___u_i_d></root>',
     '<?xml version="1.0" encoding="UTF-16"?><root><userid>KG12/10/1968</userid><column___1>0</column___1><column___2>0</column___2><column___3>0</column___3><column___4>0</column___4><column___5>0</column___5><column___6>0</column___6><column___7>0</column___7><column___8>0</column___8><column___9>0</column___9><column___1_0>0</column___1_0><column___1_1>0</column___1_1><column___1_2>0</column___1_2><column___1_3>0</column___1_3><column___1_4>0</column___1_4><column___1_5>0</column___1_5><column___1_6>0</column___1_6><column___1_7>0</column___1_7><column___1_8>0</column___1_8><column___1_9>0</column___1_9><column___2_0>0</column___2_0><column___2_1>0</column___2_1><column___D_S>0</column___D_S><column___A_S>0</column___A_S><column___S_S>0</column___S_S><userid___u_i_d>6</userid___u_i_d></root>' 	)

    Please note there are other columns in the table as shown below and we need to convert the xml code stored in the Information column as string to columns


    Hi jaggy,

    Here are the answers for your questions.

    1.

    >>

    That worked but when i added another row, I get the error below

    Msg 213, Level 16, State 1, Line 5
    Column name or number of supplied values does not match table definition.

    Below is sample data in the 2nd row. 

    <<

    You have made the values into two columns for insert, you need to modify it like this, so that it would not get that error message.

    declare @t table
    (
    x nvarchar(max)
    )
    
    insert @t
    values
    ('<?xml version="1.0" encoding="UTF-16"?><root><userid>JS30/08/1981</userid><column___1>0</column___1><column___2>1</column___2><column___3>0</column___3><column___4>0</column___4><column___5>0</column___5><column___6>0</column___6><column___7>0</column___7><column___8>0</column___8><column___9>0</column___9><column___1_0>0</column___1_0><column___1_1>0</column___1_1><column___1_2>0</column___1_2><column___1_3>0</column___1_3><column___1_4>0</column___1_4><column___1_5>0</column___1_5><column___1_6>0</column___1_6><column___1_7>0</column___1_7><column___1_8>0</column___1_8><column___1_9>0</column___1_9><column___2_0>0</column___2_0><column___2_1>0</column___2_1><column___D_S>0</column___D_S><column___A_S>2</column___A_S><column___S_S>0</column___S_S><userid___u_i_d>5</userid___u_i_d></root>'),
     ('<?xml version="1.0" encoding="UTF-16"?><root><userid>KG12/10/1968</userid><column___1>0</column___1><column___2>0</column___2><column___3>0</column___3><column___4>0</column___4><column___5>0</column___5><column___6>0</column___6><column___7>0</column___7><column___8>0</column___8><column___9>0</column___9><column___1_0>0</column___1_0><column___1_1>0</column___1_1><column___1_2>0</column___1_2><column___1_3>0</column___1_3><column___1_4>0</column___1_4><column___1_5>0</column___1_5><column___1_6>0</column___1_6><column___1_7>0</column___1_7><column___1_8>0</column___1_8><column___1_9>0</column___1_9><column___2_0>0</column___2_0><column___2_1>0</column___2_1><column___D_S>0</column___D_S><column___A_S>0</column___A_S><column___S_S>0</column___S_S><userid___u_i_d>6</userid___u_i_d></root>' 	)
    
     select * from @t

    2.

    >>

    Please note there are other columns in the table as shown below and we need to convert the xml code stored in the Information column as string to columns

    <<

    Please provide table structure with sample data, the desired output with entire image. We're not a Robot that could discover everything and provide anything you exactly want.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 4, 2019 9:25 AM
    Moderator
  • That worked but when i added another row, I get the error below

    Msg 213, Level 16, State 1, Line 5
    Column name or number of supplied values does not match table definition.

    Below is sample data in the 2nd row. 

    insert @t
    values
    ('<?xml version="1.0" encoding="UTF-16"?><root><userid>JS30/08/1981</userid><column___1>0</column___1><column___2>1</column___2><column___3>0</column___3><column___4>0</column___4><column___5>0</column___5><column___6>0</column___6><column___7>0</column___7><column___8>0</column___8><column___9>0</column___9><column___1_0>0</column___1_0><column___1_1>0</column___1_1><column___1_2>0</column___1_2><column___1_3>0</column___1_3><column___1_4>0</column___1_4><column___1_5>0</column___1_5><column___1_6>0</column___1_6><column___1_7>0</column___1_7><column___1_8>0</column___1_8><column___1_9>0</column___1_9><column___2_0>0</column___2_0><column___2_1>0</column___2_1><column___D_S>0</column___D_S><column___A_S>2</column___A_S><column___S_S>0</column___S_S><userid___u_i_d>5</userid___u_i_d></root>',
     '<?xml version="1.0" encoding="UTF-16"?><root><userid>KG12/10/1968</userid><column___1>0</column___1><column___2>0</column___2><column___3>0</column___3><column___4>0</column___4><column___5>0</column___5><column___6>0</column___6><column___7>0</column___7><column___8>0</column___8><column___9>0</column___9><column___1_0>0</column___1_0><column___1_1>0</column___1_1><column___1_2>0</column___1_2><column___1_3>0</column___1_3><column___1_4>0</column___1_4><column___1_5>0</column___1_5><column___1_6>0</column___1_6><column___1_7>0</column___1_7><column___1_8>0</column___1_8><column___1_9>0</column___1_9><column___2_0>0</column___2_0><column___2_1>0</column___2_1><column___D_S>0</column___D_S><column___A_S>0</column___A_S><column___S_S>0</column___S_S><userid___u_i_d>6</userid___u_i_d></root>' 	)

    Please note there are other columns in the table as shown below and we need to convert the xml code stored in the Information column as string to columns


    then you need to use insert tablename(column list) and then the select

    Only you know what columns you're supposed to get values from xml

    so post what columns your table has and which of those you need to get value from xml


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, January 4, 2019 9:33 AM
  • Hi Visakh,

    Please find below sample data with all columns. The Formdata column stores data in xml format and the data within the xml file needs to be converted into columns. As you will noticed, the xml records in the first two rows have different fields compared to row 4 and 5.

    declare @form TABLE
        ([FormID] int, [UserID] int, [FormData] varchar(1258))
    ;
        
    INSERT INTO @form
        ([FormID], [UserID], [FormData])
    VALUES
        (21209480, 8, '<?xml version="1.0" encoding="utf-16"?>  <root>    <sno>0</sno>    <ses_1>3</ses_1>    <ses_2>3</ses_2>    <ses_3>2</ses_3>    <ses_4>3</ses_4>    <ses_5>3</ses_5>    <ses_6>4</ses_6>    <ses_7>3</ses_7>    <ses_8>4</ses_8>    <ses_9>4</ses_9>    <ses_1_0>4</ses_1_0>    <ses>11</ses>  </root>'),
        (21209480, 9, '<?xml version="1.0" encoding="utf-16"?>  <root>    <sno>0</sno>    <ses_1>3</ses_1>    <ses_2>3</ses_2>    <ses_3>3</ses_3>    <ses_4>3</ses_4>    <ses_5>3</ses_5>    <ses_6>3</ses_6>    <ses_7>3</ses_7>    <ses_8>3</ses_8>    <ses_9>3</ses_9>    <ses_1_0>3</ses_1_0>    <ses>12</ses>  </root>'),
        (21409558, 8, '<?xml version="1.0" encoding="utf-16"?>  <root>    <userid>JS 24/04/1985</userid>    <column___1>0</column___1>    <column___2>1</column___2>    <column___3>0</column___3>    <column___4>0</column___4>    <column___5>0</column___5>    <column___6>0</column___6>    <column___7>0</column___7>    <column___8>0</column___8>    <column___9>0</column___9>    <column___1_0>0</column___1_0>    <column___1_1>0</column___1_1>    <column___1_2>0</column___1_2>    <column___1_3>0</column___1_3>    <column___1_4>1</column___1_4>    <column___1_5>0</column___1_5>    <column___1_6>0</column___1_6>    <column___1_7>0</column___1_7>    <column___1_8>0</column___1_8>    <column___1_9>0</column___1_9>    <column___2_0>0</column___2_0>    <column___2_1>0</column___2_1>    <column___D_S>0</column___D_S>    <column___A_S>2</column___A_S>    <column___S_S>2</column___S_S>    <userid___u_i_d>8</userid___u_i_d>  </root>'),
        (21409558, 9, '<?xml version="1.0" encoding="utf-16"?>  <root>    <userid>MR 30/08/1981</userid>    <column___1>0</column___1>    <column___2>1</column___2>    <column___3>0</column___3>    <column___4>0</column___4>    <column___5>0</column___5>    <column___6>0</column___6>    <column___7>0</column___7>    <column___8>0</column___8>    <column___9>0</column___9>    <column___1_0>0</column___1_0>    <column___1_1>0</column___1_1>    <column___1_2>0</column___1_2>    <column___1_3>0</column___1_3>    <column___1_4>0</column___1_4>    <column___1_5>0</column___1_5>    <column___1_6>0</column___1_6>    <column___1_7>0</column___1_7>    <column___1_8>0</column___1_8>    <column___1_9>0</column___1_9>    <column___2_0>0</column___2_0>    <column___2_1>0</column___2_1>    <column___D_S>0</column___D_S>    <column___A_S>2</column___A_S>    <column___S_S>0</column___S_S>    <userid___u_i_d>9</userid___u_i_d>  </root>')

    Below is the desired output

    FormID UserID root sno ses_1 ses_2 ses_3 ses_4 ses_5 ses_6 ses_7 ses_8 ses_9 ses_1_0 ses userid2 column___1 column___2 column___3 column___4 column___5 column___6 column___7 column___8 column___9 column___1_0 column___1_1 column___1_2 column___1_3 column___1_4 column___1_5 column___1_6 column___1_7 column___1_8 column___1_9 column___2_0 column___2_1 column___D_S column___A_S column___S_S userid___u_i_d
    21209480 8                                                    0 3 3 2 3 3 4 3 4 4 4 33                                                    
    21209480 9                                                    0 3 3 3 3 3 3 3 3 3 3 30                                                    
    21409558 8                                                                                                                                    JS 24/04/1985 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 2 2 8
    21409558 9                                                                                                                                    MR 30/08/1981 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 9

    Below is a screenshot of the output required. Not all columns are visible in the screenshot


    • Edited by jaggy99 Monday, January 7, 2019 1:36 AM
    Monday, January 7, 2019 1:31 AM
  • Hi Will,

    Please find below the sample data and desired output.

    Thanks

    Jag


    • Edited by jaggy99 Monday, January 7, 2019 1:38 AM
    Monday, January 7, 2019 1:37 AM
  • Hi Visakh,

    Please find below sample data with all columns. The Formdata column stores data in xml format and the data within the xml file needs to be converted into columns. As you will noticed, the xml records in the first two rows have different fields compared to row 4 and 5.

    declare @form TABLE
        ([FormID] int, [UserID] int, [FormData] varchar(1258))
    ;
        
    INSERT INTO @form
        ([FormID], [UserID], [FormData])
    VALUES
        (21209480, 8, '<?xml version="1.0" encoding="utf-16"?>  <root>    <sno>0</sno>    <ses_1>3</ses_1>    <ses_2>3</ses_2>    <ses_3>2</ses_3>    <ses_4>3</ses_4>    <ses_5>3</ses_5>    <ses_6>4</ses_6>    <ses_7>3</ses_7>    <ses_8>4</ses_8>    <ses_9>4</ses_9>    <ses_1_0>4</ses_1_0>    <ses>11</ses>  </root>'),
        (21209480, 9, '<?xml version="1.0" encoding="utf-16"?>  <root>    <sno>0</sno>    <ses_1>3</ses_1>    <ses_2>3</ses_2>    <ses_3>3</ses_3>    <ses_4>3</ses_4>    <ses_5>3</ses_5>    <ses_6>3</ses_6>    <ses_7>3</ses_7>    <ses_8>3</ses_8>    <ses_9>3</ses_9>    <ses_1_0>3</ses_1_0>    <ses>12</ses>  </root>'),
        (21409558, 8, '<?xml version="1.0" encoding="utf-16"?>  <root>    <userid>JS 24/04/1985</userid>    <column___1>0</column___1>    <column___2>1</column___2>    <column___3>0</column___3>    <column___4>0</column___4>    <column___5>0</column___5>    <column___6>0</column___6>    <column___7>0</column___7>    <column___8>0</column___8>    <column___9>0</column___9>    <column___1_0>0</column___1_0>    <column___1_1>0</column___1_1>    <column___1_2>0</column___1_2>    <column___1_3>0</column___1_3>    <column___1_4>1</column___1_4>    <column___1_5>0</column___1_5>    <column___1_6>0</column___1_6>    <column___1_7>0</column___1_7>    <column___1_8>0</column___1_8>    <column___1_9>0</column___1_9>    <column___2_0>0</column___2_0>    <column___2_1>0</column___2_1>    <column___D_S>0</column___D_S>    <column___A_S>2</column___A_S>    <column___S_S>2</column___S_S>    <userid___u_i_d>8</userid___u_i_d>  </root>'),
        (21409558, 9, '<?xml version="1.0" encoding="utf-16"?>  <root>    <userid>MR 30/08/1981</userid>    <column___1>0</column___1>    <column___2>1</column___2>    <column___3>0</column___3>    <column___4>0</column___4>    <column___5>0</column___5>    <column___6>0</column___6>    <column___7>0</column___7>    <column___8>0</column___8>    <column___9>0</column___9>    <column___1_0>0</column___1_0>    <column___1_1>0</column___1_1>    <column___1_2>0</column___1_2>    <column___1_3>0</column___1_3>    <column___1_4>0</column___1_4>    <column___1_5>0</column___1_5>    <column___1_6>0</column___1_6>    <column___1_7>0</column___1_7>    <column___1_8>0</column___1_8>    <column___1_9>0</column___1_9>    <column___2_0>0</column___2_0>    <column___2_1>0</column___2_1>    <column___D_S>0</column___D_S>    <column___A_S>2</column___A_S>    <column___S_S>0</column___S_S>    <userid___u_i_d>9</userid___u_i_d>  </root>')

    Below is the desired output

    FormID UserID root sno ses_1 ses_2 ses_3 ses_4 ses_5 ses_6 ses_7 ses_8 ses_9 ses_1_0 ses userid2 column___1 column___2 column___3 column___4 column___5 column___6 column___7 column___8 column___9 column___1_0 column___1_1 column___1_2 column___1_3 column___1_4 column___1_5 column___1_6 column___1_7 column___1_8 column___1_9 column___2_0 column___2_1 column___D_S column___A_S column___S_S userid___u_i_d
    21209480 8                                                    0 3 3 2 3 3 4 3 4 4 4 33                                                    
    21209480 9                                                    0 3 3 3 3 3 3 3 3 3 3 30                                                    
    21409558 8                                                                                                                                    JS 24/04/1985 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 2 2 8
    21409558 9                                                                                                                                    MR 30/08/1981 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 9

    Below is a screenshot of the output required. Not all columns are visible in the screenshot


    OK, please try

    declare @form TABLE ([FormID] int, [UserID] int, [FormData] varchar(1258)) ; INSERT INTO @form ([FormID], [UserID], [FormData]) VALUES (21209480, 8, '<?xml version="1.0" encoding="utf-16"?> <root> <sno>0</sno> <ses_1>3</ses_1> <ses_2>3</ses_2> <ses_3>2</ses_3> <ses_4>3</ses_4> <ses_5>3</ses_5> <ses_6>4</ses_6> <ses_7>3</ses_7> <ses_8>4</ses_8> <ses_9>4</ses_9> <ses_1_0>4</ses_1_0> <ses>11</ses> </root>'), (21209480, 9, '<?xml version="1.0" encoding="UTF-16"?> <root> <sno>0</sno> <ses_1>3</ses_1> <ses_2>3</ses_2> <ses_3>3</ses_3> <ses_4>3</ses_4> <ses_5>3</ses_5> <ses_6>3</ses_6> <ses_7>3</ses_7> <ses_8>3</ses_8> <ses_9>3</ses_9> <ses_1_0>3</ses_1_0> <ses>12</ses> </root>'), (21409558, 8, '<?xml version="1.0" encoding="utf-16"?> <root> <userid>JS 24/04/1985</userid> <column___1>0</column___1> <column___2>1</column___2> <column___3>0</column___3> <column___4>0</column___4> <column___5>0</column___5> <column___6>0</column___6> <column___7>0</column___7> <column___8>0</column___8> <column___9>0</column___9> <column___1_0>0</column___1_0> <column___1_1>0</column___1_1> <column___1_2>0</column___1_2> <column___1_3>0</column___1_3> <column___1_4>1</column___1_4> <column___1_5>0</column___1_5> <column___1_6>0</column___1_6> <column___1_7>0</column___1_7> <column___1_8>0</column___1_8> <column___1_9>0</column___1_9> <column___2_0>0</column___2_0> <column___2_1>0</column___2_1> <column___D_S>0</column___D_S> <column___A_S>2</column___A_S> <column___S_S>2</column___S_S> <userid___u_i_d>8</userid___u_i_d> </root>'), (21409558, 9, '<?xml version="1.0" encoding="utf-16"?> <root> <userid>MR 30/08/1981</userid> <column___1>0</column___1> <column___2>1</column___2> <column___3>0</column___3> <column___4>0</column___4> <column___5>0</column___5> <column___6>0</column___6> <column___7>0</column___7> <column___8>0</column___8> <column___9>0</column___9> <column___1_0>0</column___1_0> <column___1_1>0</column___1_1> <column___1_2>0</column___1_2> <column___1_3>0</column___1_3> <column___1_4>0</column___1_4> <column___1_5>0</column___1_5> <column___1_6>0</column___1_6> <column___1_7>0</column___1_7> <column___1_8>0</column___1_8> <column___1_9>0</column___1_9> <column___2_0>0</column___2_0> <column___2_1>0</column___2_1> <column___D_S>0</column___D_S> <column___A_S>2</column___A_S> <column___S_S>0</column___S_S> <userid___u_i_d>9</userid___u_i_d> </root>') ;WITH CTE AS ( SELECT [FormID], [UserID], CAST(CAST([FormData] as nvarchar(max)) AS XML) AS FormData FROM @form ) SELECT s.FormID, s.UserID, s.FormData, s.[root], s.sno, s.ses_1, s.ses_2, s.ses_3, s.ses_4, s.ses_5, s.ses_6, s.ses_7, s.ses_8, s.ses_9, s.ses_1_0, s.ses_1+s.ses_2+s.ses_3+s.ses_4+s.ses_5+s.ses_6+s.ses_7+s.ses_8+s.ses_9+s.ses_1_0 as ses, s.userid2, s.column___1, s.column___2, s.column___3, s.column___4, s.column___5, s.column___6, s.column___7 FROM ( SELECT T.FormID, T.UserID, T.FormData, '' as [root], n.c.value('sno[1]','int') as [sno], n.c.value('ses_1[1]','int') as [ses_1], n.c.value('ses_2[1]','int') as [ses_2], n.c.value('ses_3[1]','int') as [ses_3], n.c.value('ses_4[1]','int') as [ses_4], n.c.value('ses_5[1]','int') as [ses_5], n.c.value('ses_6[1]','int') as [ses_6], n.c.value('ses_7[1]','int') as [ses_7], n.c.value('ses_8[1]','int') as [ses_8], n.c.value('ses_9[1]','int') as [ses_9], n.c.value('ses_1_0[1]','int') as [ses_1_0], n.c.value('ses[1]','int') as [ses], n.c.value('userid[1]','varchar(30)') as [userid2], n.c.value('column___1[1]','int') as [column___1], n.c.value('column___2[1]','int') as [column___2], n.c.value('column___3[1]','int') as [column___3], n.c.value('column___4[1]','int') as [column___4], n.c.value('column___5[1]','int') as [column___5], n.c.value('column___6[1]','int') as [column___6], n.c.value('column___7[1]','int') as [column___7] FROM CTE T CROSS APPLY T.FormData.nodes('/root') as n(c) ) S

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 8, 2019 7:28 AM
    Moderator
  • Hi Visakh,

    Please find below sample data with all columns. The Formdata column stores data in xml format and the data within the xml file needs to be converted into columns. As you will noticed, the xml records in the first two rows have different fields compared to row 4 and 5.

    declare @form TABLE
        ([FormID] int, [UserID] int, [FormData] varchar(1258))
    ;
        
    INSERT INTO @form
        ([FormID], [UserID], [FormData])
    VALUES
        (21209480, 8, '<?xml version="1.0" encoding="utf-16"?>  <root>    <sno>0</sno>    <ses_1>3</ses_1>    <ses_2>3</ses_2>    <ses_3>2</ses_3>    <ses_4>3</ses_4>    <ses_5>3</ses_5>    <ses_6>4</ses_6>    <ses_7>3</ses_7>    <ses_8>4</ses_8>    <ses_9>4</ses_9>    <ses_1_0>4</ses_1_0>    <ses>11</ses>  </root>'),
        (21209480, 9, '<?xml version="1.0" encoding="utf-16"?>  <root>    <sno>0</sno>    <ses_1>3</ses_1>    <ses_2>3</ses_2>    <ses_3>3</ses_3>    <ses_4>3</ses_4>    <ses_5>3</ses_5>    <ses_6>3</ses_6>    <ses_7>3</ses_7>    <ses_8>3</ses_8>    <ses_9>3</ses_9>    <ses_1_0>3</ses_1_0>    <ses>12</ses>  </root>'),
        (21409558, 8, '<?xml version="1.0" encoding="utf-16"?>  <root>    <userid>JS 24/04/1985</userid>    <column___1>0</column___1>    <column___2>1</column___2>    <column___3>0</column___3>    <column___4>0</column___4>    <column___5>0</column___5>    <column___6>0</column___6>    <column___7>0</column___7>    <column___8>0</column___8>    <column___9>0</column___9>    <column___1_0>0</column___1_0>    <column___1_1>0</column___1_1>    <column___1_2>0</column___1_2>    <column___1_3>0</column___1_3>    <column___1_4>1</column___1_4>    <column___1_5>0</column___1_5>    <column___1_6>0</column___1_6>    <column___1_7>0</column___1_7>    <column___1_8>0</column___1_8>    <column___1_9>0</column___1_9>    <column___2_0>0</column___2_0>    <column___2_1>0</column___2_1>    <column___D_S>0</column___D_S>    <column___A_S>2</column___A_S>    <column___S_S>2</column___S_S>    <userid___u_i_d>8</userid___u_i_d>  </root>'),
        (21409558, 9, '<?xml version="1.0" encoding="utf-16"?>  <root>    <userid>MR 30/08/1981</userid>    <column___1>0</column___1>    <column___2>1</column___2>    <column___3>0</column___3>    <column___4>0</column___4>    <column___5>0</column___5>    <column___6>0</column___6>    <column___7>0</column___7>    <column___8>0</column___8>    <column___9>0</column___9>    <column___1_0>0</column___1_0>    <column___1_1>0</column___1_1>    <column___1_2>0</column___1_2>    <column___1_3>0</column___1_3>    <column___1_4>0</column___1_4>    <column___1_5>0</column___1_5>    <column___1_6>0</column___1_6>    <column___1_7>0</column___1_7>    <column___1_8>0</column___1_8>    <column___1_9>0</column___1_9>    <column___2_0>0</column___2_0>    <column___2_1>0</column___2_1>    <column___D_S>0</column___D_S>    <column___A_S>2</column___A_S>    <column___S_S>0</column___S_S>    <userid___u_i_d>9</userid___u_i_d>  </root>')

    Below is the desired output

    FormID UserID root sno ses_1 ses_2 ses_3 ses_4 ses_5 ses_6 ses_7 ses_8 ses_9 ses_1_0 ses userid2 column___1 column___2 column___3 column___4 column___5 column___6 column___7 column___8 column___9 column___1_0 column___1_1 column___1_2 column___1_3 column___1_4 column___1_5 column___1_6 column___1_7 column___1_8 column___1_9 column___2_0 column___2_1 column___D_S column___A_S column___S_S userid___u_i_d
    21209480 8                                                    0 3 3 2 3 3 4 3 4 4 4 33                                                    
    21209480 9                                                    0 3 3 3 3 3 3 3 3 3 3 30                                                    
    21409558 8                                                                                                                                    JS 24/04/1985 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 2 2 8
    21409558 9                                                                                                                                    MR 30/08/1981 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0 9

    Below is a screenshot of the output required. Not all columns are visible in the screenshot


    You can do like this

    declare @form TABLE
        ([FormID] int, [UserID] int, [FormData] nvarchar(1258))
    ;
        
    INSERT INTO @form
        ([FormID], [UserID], [FormData])
    VALUES
        (21209480, 8, '<?xml version="1.0" encoding="utf-16"?>  <root>    <sno>0</sno>    <ses_1>3</ses_1>    <ses_2>3</ses_2>    <ses_3>2</ses_3>    <ses_4>3</ses_4>    <ses_5>3</ses_5>    <ses_6>4</ses_6>    <ses_7>3</ses_7>    <ses_8>4</ses_8>    <ses_9>4</ses_9>    <ses_1_0>4</ses_1_0>    <ses>11</ses>  </root>'),
        (21209480, 9, '<?xml version="1.0" encoding="utf-16"?>  <root>    <sno>0</sno>    <ses_1>3</ses_1>    <ses_2>3</ses_2>    <ses_3>3</ses_3>    <ses_4>3</ses_4>    <ses_5>3</ses_5>    <ses_6>3</ses_6>    <ses_7>3</ses_7>    <ses_8>3</ses_8>    <ses_9>3</ses_9>    <ses_1_0>3</ses_1_0>    <ses>12</ses>  </root>'),
        (21409558, 8, '<?xml version="1.0" encoding="utf-16"?>  <root>    <userid>JS 24/04/1985</userid>    <column___1>0</column___1>    <column___2>1</column___2>    <column___3>0</column___3>    <column___4>0</column___4>    <column___5>0</column___5>    <column___6>0</column___6>    <column___7>0</column___7>    <column___8>0</column___8>    <column___9>0</column___9>    <column___1_0>0</column___1_0>    <column___1_1>0</column___1_1>    <column___1_2>0</column___1_2>    <column___1_3>0</column___1_3>    <column___1_4>1</column___1_4>    <column___1_5>0</column___1_5>    <column___1_6>0</column___1_6>    <column___1_7>0</column___1_7>    <column___1_8>0</column___1_8>    <column___1_9>0</column___1_9>    <column___2_0>0</column___2_0>    <column___2_1>0</column___2_1>    <column___D_S>0</column___D_S>    <column___A_S>2</column___A_S>    <column___S_S>2</column___S_S>    <userid___u_i_d>8</userid___u_i_d>  </root>'),
        (21409558, 9, '<?xml version="1.0" encoding="utf-16"?>  <root>    <userid>MR 30/08/1981</userid>    <column___1>0</column___1>    <column___2>1</column___2>    <column___3>0</column___3>    <column___4>0</column___4>    <column___5>0</column___5>    <column___6>0</column___6>    <column___7>0</column___7>    <column___8>0</column___8>    <column___9>0</column___9>    <column___1_0>0</column___1_0>    <column___1_1>0</column___1_1>    <column___1_2>0</column___1_2>    <column___1_3>0</column___1_3>    <column___1_4>0</column___1_4>    <column___1_5>0</column___1_5>    <column___1_6>0</column___1_6>    <column___1_7>0</column___1_7>    <column___1_8>0</column___1_8>    <column___1_9>0</column___1_9>    <column___2_0>0</column___2_0>    <column___2_1>0</column___2_1>    <column___D_S>0</column___D_S>    <column___A_S>2</column___A_S>    <column___S_S>0</column___S_S>    <userid___u_i_d>9</userid___u_i_d>  </root>')
    
    
    	IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
    
    	SELECT FormID,UserID AS UserNo,n.value('local-name(.)','varchar(100)') as ColName,n.value('.','varchar(100)') as ColValue
    	INTO #temp
    	FROM (select FormID,UserID,CAST(FormData as xml) as x from @form) f
    	cross apply x.nodes('/root/*')m(n)
    
    	DECLARE @Cols varchar(max)
    
    	SET @Cols = STUFF((SELECT ',[' + ColName +']' FROM (SELECT DISTINCT ColName FROM #Temp)t FOR XML PATH('')),1,1,'')
    
    
    	DECLARE @SQL varchar(max) = 'SELECT * FROM #Temp PIVOT(MAX(ColValue) FOR COlName IN (' + @Cols + '))p'
    
    	EXEC 	(@SQL)
    
    
    /*
    Output
    ----------------------------------------------------------
    FormID	UserNo	column___1	column___1_0	column___1_1	column___1_2	column___1_3	column___1_4	column___1_5	column___1_6	column___1_7	column___1_8	column___1_9	column___2	column___2_0	column___2_1	column___3	column___4	column___5	column___6	column___7	column___8	column___9	column___A_S	column___D_S	column___S_S	ses	ses_1	ses_1_0	ses_2	ses_3	ses_4	ses_5	ses_6	ses_7	ses_8	ses_9	sno	userid	userid___u_i_d
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    21209480	8	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	11	3	4	3	2	3	3	4	3	4	4	0	NULL	NULL
    21409558	8	0	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	2	0	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	JS 24/04/1985	8
    21209480	9	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	12	3	3	3	3	3	3	3	3	3	3	0	NULL	NULL
    21409558	9	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	2	0	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	MR 30/08/1981	9
    
    
    
    
    */


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, January 8, 2019 7:51 AM