CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
Ashutosh
Ashutosh

Reputation: 111

Store XML Tags in Table for Different XML Size in SQL Server

I have code in SQL Server which extracts the tags from XML and stores them in a temp table (#result). Right now the cursor runs in a loop for each XML assuming that structure of all XMLs is same. Example below:

XML1 : <Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3></Root>

result Table:

Tag1    |   Tag2    |   Tag3
--------|-----------|--------
val1    |   val2    |   val3

But now we have some XMLs which can have less number of tags. Example below:

XML1 : <Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3></Root>
XML2 : <Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3><Tag4>val4</Tag4></Root>
XML3 : <Root><Tag1>val1</Tag1><Tag2>val2</Tag2></Root>

result table:

Tag1    |   Tag2    |   Tag3    |   Tag4
--------|-----------|-----------|--------
val1    |   val2    |   val3    |       
--------|-----------|-----------|--------
val1    |   val2    |   val3    |   val4    
--------|-----------|-----------|--------
val1    |   val2    |           |

Below is my existing cursor code.

Open C_XML
Fetch next from C_XML into @input_xml
    while @@FETCH_STATUS = 0
    Begin
     Create Table #MyTempTable (
       name varchar(max),
       value varchar(max)
    );
    insert into #MyTempTable
    SELECT
    bar.value('local-name(.)','VARCHAR(max)') as name,
    bar.value('./.','VARCHAR(max)')  as value
    FROM
    @input_xml.nodes('/Root/*') AS input_xml(bar)
    DECLARE @name NVARCHAR(MAX) = ''
    DECLARE @val NVARCHAR(MAX) = ''
    Declare @Query NVARCHAR(MAX) = ''
    SELECT  @name +=   QUOTENAME(name)+ ','
    FROM
    (
        SELECT name
        FROM #MyTempTable
    ) AS ColName
    -- remvoing last comma
    SET @name = LEFT(@name, LEN(@name)-1)
    SET @Query =
    'SELECT * INTO ##temp2 FROM
    (SELECT
        name,
        value
    FROM
        #MyTempTable
    )
    AS TempTable
    PIVOT(
        max(value)
        FOR name IN (' + @name +')
    ) AS SPivotTable'

    EXECUTE sp_executesql @Query
    drop table #MyTempTable
    select * from ##temp2
    if (@count = 1 )
    begin
        select * into #result from ##temp2
    end
    else 
    begin
        insert into #result     
            select * from ##temp2
    end
    drop table ##temp2

    Set @count = @count + 1

Fetch next from C_XML  into @input_xml
END   
CLOSE C_XML;  
DEALLOCATE C_XML;

Please suggest how I can enhance this code to make it dynamic for all type of XMLs as mentioned in above example. Currently its giving error:

#result table definition is not correct.

Upvotes: 0

Views: 53

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

If I get this correctly, you would have first to analyse each and any XML to find any existing element name. With this list of names you have to build a SELECT generically, covering any element name anywhere in your table...

I think this is the wrong approach...

Furthermore, there are very few cases, where a CURSOR is actually needed. In almost any case, if you think you'd need a cursor, you should rethink your approach.

I'd suggest first to create a classical EAV-list as a staging table:

--A mockup to simulate your issue

DECLARE @YourTable TABLE(ID INT IDENTITY, YourXml XML);
INSERT INTO @YourTable VALUES ('<Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3></Root>')
,('<Root><Tag1>val1</Tag1><Tag2>val2</Tag2><Tag3>val3</Tag3><Tag4>val4</Tag4></Root>')
,('<Root><Tag1>val1</Tag1><Tag2>val2</Tag2></Root>');

--a set-based approach to get every value without the need of a cursor

SELECT t.ID
      ,A.AnyNode.value('local-name(.)','nvarchar(max)') AS ElementName
      ,A.AnyNode.value('text()[1]','nvarchar(max)') AS ElementValue
FROM @YourTable t
CROSS APPLY t.YourXml.nodes('/Root/*') A(AnyNode);

With this result you can proceed with standard SQL. If needed, you can create your dynamic SQL from here...

Upvotes: 2

Related Questions