.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> SQL Query >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Passing XML to sql server and creating a table dynalically in sql server 2008

Posted By:Maverick       Posted Date: July 26, 2011    Points: 40    Category: SQL Query    URL: http://www.dotnetspark.com  

Passing XML to sql server and creating a table dynamically in sql server 2008
 

Hello All,

We can use the xml data type to create the table in sql server 2008. Suppose we are passing the following xml from the application server.

The best part is we are not required to specify the column names statically. It generates the table dynamically as per the xml passed.

Consider the following xml


 
    55
    Test1
    Ahmedabad
 

 
  56
    Test2
    Surat
 

 
  55
    Test3
    Baroda
 



The store d procedure below will iterate through the nodes of the xml and will create a temp table with "UserID", "Name" and "City" column and the related data to it.

ALTER PROCEDURE [dbo].[AP_RunListBulkUpdate]
    (
      @XMLData XML
    )
  AS
    BEGIN 
 
        SET NOCOUNT ON 
----Drop temp table if exists.
        IF OBJECT_ID('tempdb..##tempSample') IS NOT NULL
            BEGIN   
                DROP TABLE ##tempSample   
            END
        DECLARE @NodeLevel INT ,
            @cmd NVARCHAR(MAX) ,
            @xmlQuery NVARCHAR(MAX) ,
            @columns VARCHAR(MAX) ,
            @finalColumns VARCHAR(MAX)

----Declare temp table to read the xml and create table to generate xml condition.
        CREATE TABLE #Nodes
            (
              NodeLevel INT NOT NULL ,
              RootName NVARCHAR(MAX) NOT NULL ,
              ElementName NVARCHAR(MAX) NOT NULL
            )

----Begin at root level
        SET @NodeLevel = 1

----Iterate all levels until no more levels found
        WHILE @@ROWCOUNT > 0
            BEGIN
            ----Build a dynamic SQL string for each level
                SELECT  @cmd = 'SELECT  DISTINCT
                                     ' + STR(@NodeLevel)
                        + ',
                                     t.n.value(''local-name(..)[1]'', ''VARCHAR(MAX)'') AS RootName,
                                     t.n.value(''local-name(.)[1]'', ''VARCHAR(MAX)'') AS ElementName
                             FROM    @n.nodes(''' + REPLICATE('/*', @NodeLevel)
                        + ''') AS t(n)' ,
                        @NodeLevel = @NodeLevel + 1

            ----Store the result in the temp table
                INSERT  #Nodes
                        ( NodeLevel ,
                          RootName ,
                          ElementName
                        )
                        EXEC sp_executesql @cmd, N'@n XML', @n = @XMLData
            END

----Reveal the XML structure.
        SELECT  NodeLevel ,
                RootName ,
                ElementName
        FROM    #Nodes

----Build Dynamic SQL for inserting xml data.
        SELECT  @columns = SUBSTRING(( SELECT   ' xmlVals.rowvals.query('''
                                                + #Nodes.ElementName
                                                + ''').value(''.'', ''VARCHAR(MAX)'') AS '''
                                                + #Nodes.ElementName + ''','
                                       FROM     #Nodes
                                       WHERE    #Nodes.ElementName <> 'BulkData'
                                                AND #Nodes.ElementName <> 'Element'
                                     FOR
                                       XML PATH('')
                                     ), 0, 200000)
        SET @columns = SUBSTRING(@columns, 1, LEN(@columns) - 1)
       
----Build the complete query.       
        SET @xmlQuery = 'SELECT  ' + @columns
            + '
        INTO    ##tempSample
        FROM    @XMLData.nodes(''/BulkData/Element'') AS xmlVals ( rowvals )   
        '  
-----Execute the Dynamic sql.       
        EXECUTE sp_executesql @xmlQuery, N'@XMLData XML', @XMLData = @XMLData
        END


Hope it helps as it helped me a lot. And of course the performance is awesome.

Thanks,
Maverick





     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend