Convert XML to relational form using attribute values

  • I am trying to build relational table from the XML below using the attribute values where FIELD1 is the column and "ABC" would be the value. I can do it using the select statement below but I don't want to create case statement for each of the fields and I don't always know all the fields. Does anyone have any suggestions on how to do this?

    DECLARE @Temp TABLE (Row_Id int, TempXml xml)

    INSERT INTO @Temp(Row_Id, TempXml) VALUES (1, '

    ')

    SELECT

    Row_Id,

    MAX(CASE WHEN C.value('./@_KEY','NVARCHAR(MAX)') = 'FIELD1' THEN Convert( varchar(max) , C.value('(/DATA/FIELD[@_KEY="FIELD1"]/@_VALUE)[1]' , 'varchar(max)')) ELSE '' END) AS FIELD1,

    MAX(CASE WHEN C.value('./@_KEY','NVARCHAR(MAX)') = 'FIELD2' THEN Convert( varchar(max) , C.value('(/DATA/FIELD[@_KEY="FIELD2"]/@_VALUE)[1]' , 'varchar(max)')) ELSE '' END) AS FIELD2

    FROM @Temp Cross APPLY TempXml.nodes('DATA/FIELD') T(C)

    GROUP BY Row_Id

    Thanks Tim

  • Hi Tim,

    are the values for _KEY in your real scenario always numbered with prefix (like FIELD1, FIELD2, .., FIELD102)?

    If true, there would be a way to get the result without all the CASE statements by using dynamic SQL.

    Please clarify.

    BTW: Thanx for providing sample data ready-to-use! Good job!!

    Edit: clarification not required. Solution shown in next post works regardless of having _KEY numbered in any way...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • I just used FIELD1 and FIELD2 for the example. The _KEY attribute value can be anything. I would be interested in seeing the dynamic sql example.

    Tim

  • Hi Tim,

    sorry for the mistake in my previous post. I've been typing while thinking, not after...

    The following code should work regardless of _KEY value structure.

    Side note: If possible, please avoid using NVARCHAR(MAX) for your column definition since it seems to be oversized. It doesn't make sense to use NVARCHAR(MAX) for column definition... I used NVARCHAR(30) instead.

    -- create a temp table to store the XML data in relational, yet denormalized format

    CREATE TABLE #Temp2 (Row_Id INT, _Value NVARCHAR(30), _Key VARCHAR(30))

    -- fill the table

    INSERT INTO #Temp2

    SELECT

    Row_Id,

    C.value('./@_VALUE' , 'NVARCHAR(30)'),

    C.value('./@_KEY','NVARCHAR(30)')

    FROM @Temp Cross APPLY TempXml.nodes('DATA/FIELD') T(C)

    /* intermediate result (SELECT * FROM #Temp2)

    Row_Id_Value_Key

    1ABCFIELD1

    1XYZFIELD2

    */

    -- build the dynamic SQL string

    -- this statement generates all the CASE statements depending on the existing values in column _KEY

    DECLARE @sql NVARCHAR(MAX)

    SET @sql = N'SELECT Row_Id'

    SELECT @sql = @sql + ',MAX(CASE WHEN _KEY='''+a._KEY+''' THEN _Value ELSE '''' END) AS ['+ a._KEY+']'

    FROM #Temp2 a

    GROUP BY a._KEY

    ORDER BY a._KEY

    SET @sql = @sql + N'

    FROM #Temp2

    GROUP BY Row_Id

    ORDER BY Row_Id'

    PRINT @sql -- For Debugging

    --EXEC sp_executesql @sql -- for showing the result of the dynamic SQL

    /* final result

    Row_IdFIELD1FIELD2

    1ABCXYZ

    */



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Lutz,

    I did not want to build the Temp2 table but the way you are building the dynamic SQL with the case statement is pretty cool. I wanted to do it all in one SQL statement but this should work.

    Thank You,

    Tim

  • The reason why I used a "real" temp table is that you cannot use a table variable within a dynamic SQL unless you declare it inside the SQL statement.

    Reason: a table variable is used like a local variable whereas a temp table is valid within a session.

    Since I had to store the data in a separate table anyway I decided to store it in a more convenient way...

    The second reason is that you cannot use XML methods within a GROUP BY clause.

    Therefore, the dynamic build of the SQL statement would result in false results, since you'd need to leave the GROUP BY out of the inner part of the dynamic SQL.

    If you'd come up with a way to get the results directly from your xml structure I'd like you to post the code so I can learn from it (couldn't figure out how to do it so until now - always failed...). Thanx in advance!



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Lutz,

    Is there a way to use TOP 10 clause so that it returns 10 rows of the XML strings and not just the first 10 values from a the XML string.

    SELECT

    Row_Id,

    C.value('./@_VALUE' , 'NVARCHAR(30)'),

    C.value('./@_KEY','NVARCHAR(30)')

    FROM @Temp Cross APPLY TempXml.nodes('DATA/FIELD') T(C)

    Tim

  • TT (6/5/2009)


    SELECT

    Row_Id,

    C.value('./@_VALUE' , 'NVARCHAR(30)'),

    C.value('./@_KEY','NVARCHAR(30)')

    FROM @Temp Cross APPLY TempXml.nodes('DATA/FIELD') T(C)

    Just change your FROM clause to

    FROM (SELECT TOP 10 * FROM @Temp ORDER BY Row_Id) as sub Cross APPLY TempXml.nodes('DATA/FIELD') T(C)

    This will reduce the rows from @temp that are used for the XML conversion.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply