June 5, 2009 at 12:19 pm
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
June 5, 2009 at 2:20 pm
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...
June 5, 2009 at 2:46 pm
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
June 5, 2009 at 2:47 pm
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
*/
June 5, 2009 at 3:35 pm
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
June 5, 2009 at 4:11 pm
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!
June 5, 2009 at 4:22 pm
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
June 5, 2009 at 4:56 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply