We help IT Professionals succeed at work.

Converting XML to a hash table TSQL

DeltaFS
DeltaFS asked
on
Hi

I am using a stored proc in SQL server 2005

My proc is recieving an XMLTyped stream using the schema http://www.w3.org/2001/XMLSchema. (The same schema used in Datatable.writeXML). In the proc I want to dynamically create a hash table having the column names and data  in the XML transfered into the hash table.

I need this to be a generic process so I can't code any mapping as different column names and indeed a different number of columns could be received in the XML. I have looked for code examples on the net of how to do this but currently with no luck

Any ideas.

P.S. In preference I would prefer to use a table var as the stucture of the table has to be able to change I don't think this is possible

Many thanks for your help

Rich
Comment
Watch Question

Commented:
Is hash table temporary or regular table? If this is temporary table what exactly are you doing with this after you shred xml there?

Speaking of temporary tables (not temporary table variables), it would be extremely hard to do something in SQL. Those tables are available in the call stack - i.e. you can create it and next call subsequent stored procedures and/or dynamic sql which references that table.

Although I personally would move the logic which parses xml schema and create temporary table and shred xml sql statement to the client layer. So I would do something like:
1. Client analyzes the schema and call "create table #HashTable(..)" statement.
2. Client call SPs in the same connection with statement above providing dynamic sql which shred xml as parameter. Same connection is critical.

Something like:

create proc ProcessXml(
  @XMLData xml,
  @ShredXmlSQL nvarchar(max)
)
as
begin
  -- @ShredXmlSQL should be something like:
  -- insert into #HashTable(..)
  --    select @xml.value... -- code which shred xml

  exec sp_executesql @ShredXmlSQL, N'@XML xml', @XML = @XmlData
  ..
  --#Hash table is populated and accessible here
end

Author

Commented:
The hash table is a temporary table. I will use this table as a reference to insert, update and delete regular tables. If it was possible I would prefer to use the table variable in the proc?? but as the table structure has to be defined and the columns in the XML would change I don't see how this is possible.

I like you solution, as with everything changing the problem is usually the best solution. I will try your suggestion but put the code in the CLR and let you know how it goes.

thanks
Commented:
Temporary table variable would not work. You cannot access nor alter it from within the stack/dynamic sql.

As for CLR - it's interesting question. I have never tried to access temporary tables created in outer T-SQL procedure from within CLR one, so I'm not sure if it works.

Even if it works for bare minimum you have to create (1 column) table in TSQL procedure before you call CLR procedure and alter the table (add columns) in CLR one. Otherwise you would not be able to access the table in outer procedure (regardless of if inner procedure is TSQL or CLR).