Alexander ZInchenko Alexander ZInchenko - 2 months ago 8
SQL Question

Get data back from "select for xml raw"

I have an query with select for xml raw statment. Result looks like

<row link="someLink" DATASOURCE="SomeSystem" obj_id="1" err_id="1" />
<row link="someLink" DATASOURCE="SomeSystem" obj_id="1" err_id="2" />


or

<row UWI="123" Xcord="2.4" obj_id="1" err_id="1" />
<row UWI="122" Xcord="1.4"obj_id="1" err_id="2" />


and all xml stored in one cell. Each cell can have different attribute names and quantity.

I want to select it back from xml into table like

link____|DATASOURCE |obj_id|err_id
someLink|SomeSystem__|1____ |1

someLink|SomeSystem__|1____ |2


But node attributes are dynamic.

I can get list of id and values with

select
t.c.value('local-name(.)', 'nvarchar(128)') as [id],
t.c.value('.', 'nvarchar(128)') as [value]
from @XMLVal.nodes('row/@*') as t(c)

Answer

It is much easier - in cases where you know your target in detail - to address the elements and attributes of an XML directly with an XPath.

Your approach is perfectly OK for generic reading of an unknown XML - but much to complicated here. You'd have to first read your attributes row-wise and then have to perform some kind of PIVOT or GROUP BY with Max(CASE... ) to get them as plain table back.

Be aware, that your XML seems to have not root element. This is possible in SQL queries, but - strictly seen - not valid...

DECLARE @XMLVal XML=
'<row link="someLink" DATASOURCE="SomeSystem" obj_id="1" err_id="1" />
<row link="someLink" DATASOURCE="SomeSystem" obj_id="1" err_id="2" />';

select 
   t.c.value('@link', 'nvarchar(128)') as link,
   t.c.value('@DATASOURCE', 'nvarchar(128)') as DATASOURCE,
   t.c.value('@obj_id', 'int') as obj_id,
   t.c.value('@err_id', 'int') as err_id
from @XMLVal.nodes('/row') as t(c)

UPDATE

As you elaborated your XML is coming in completely different shapes. To reach your output target you have to create column names dynamically. If this dynamically, but bound to a closed set, I'd suggest some kind of PIVOT. But - in your case - this seems to be completely free. This must be done with dynamic SQL. This means, you create a statement as string (exactly as you would need to type it in order to achieve the correct result. Than you use EXEC to execute this command.

One backdraw is, that you cannot use this in ad-hoc SQL.

Try this and set the variable @RowID to 1 or to 2:

CREATE TABLE #tbl(ID INT IDENTITY,XMLVal XML);
INSERT INTO #tbl VALUES
 ('<row link="someLink" DATASOURCE="SomeSystem" obj_id="1" err_id="1" />
   <row link="someLink" DATASOURCE="SomeSystem" obj_id="1" err_id="2" />')
,('<row UWI="123" Xcord="2.4" obj_id="1" err_id="1" /> 
   <row lUWI="122" Xcord="1.4" obj_id="1" err_id="2" />');


DECLARE @RowID INT=2;

DECLARE @columnNames AS NVARCHAR(MAX)=
STUFF(
        (
        SELECT DISTINCT ',' + t.c.value('local-name(.)', 'nvarchar(128)') 
        FROM #tbl AS tbl
        CROSS APPLY XMLVal.nodes('row/@*') as t(c) 
        WHERE ID=@RowID
        FOR XML PATH('')
        ),1,1,''
      ); 

DECLARE @cmd NVARCHAR(MAX)=
  N'SELECT p.*
    FROM
    (
    SELECT 
       t.c.value(''local-name(.)'', ''nvarchar(128)'') as [colname],
       t.c.value(''.'', ''nvarchar(128)'') as [value]
    FROM #tbl AS tbl
    CROSS APPLY XMLVal.nodes(''row/@*'') as t(c)
    WHERE ID=' + CAST(@RowID AS NVARCHAR(10)) +
    ') AS tbl
    PIVOT
    (
        MAX(value) FOR colname IN(' +  @columnNames + ')
    ) AS p';

    EXEC(@cmd);
GO
DROP TABLE #tbl;
Comments