XML data into SQL Tables

Question

Hi,

I have a xml in C# code and has to store in sql table (4 tables). what is best way to insert xml records in tables

1. send XML string to Stored procedure and there we store xml data into multiple tables?
2. using SQL bulk copy in c# and store it in table?

Note: In the second one, when i insert 4 datatables into table (sql bulkcopy), if 3rd table gets an error the previous 2 tables data still remains. it wont be.

please any one suggest me

Thanks
Kasim

Answer 1

@kasim mohamed ,

Overall, data (XML or not) should be processed on a server. Specially, data of a large volume.

SQL Server has a built-in XML data type, among many others like INT, NVARCHAR(...), etc.
XML data type could be a column, a variable, or a stored procedure parameter.
SQL Server supports XPath 2.0, XQuery 1.0, and XSD 1.0 based on the w3c standards on the database engine level. All this starting from SQL Server 2005 onwards. And their implementation has nothing to do with the .Net Framework APIs.

There are multiple ways how to handle your objective:

• SSIS via its XML Source Adapter.
• SQLXML API.
• T-SQL and XQuery.
• etc.

Here is a conceptual T-SQL and XQuery example how to load hierarchical XML into a one-to-many relational database tables.

SQL

-- DDL and sample data population, start  
USE tempdb;  
GO  
  
DROP TABLE IF EXISTS #city;  
DROP TABLE IF EXISTS #state;  
  
-- parent table  
CREATE TABLE #state  (  
 stateID INT IDENTITY PRIMARY KEY,   
 stateName VARCHAR(30),   
 abbr CHAR(2),   
 capital VARCHAR(30)  
);  
-- child table (1-to-many)  
CREATE TABLE #city (  
 cityID INT IDENTITY,   
 stateID INT NOT NULL FOREIGN KEY REFERENCES #state(stateID),   
 city VARCHAR(30),   
 [population] INT,  
 PRIMARY KEY (cityID, stateID, city)  
);  
-- mapping table to preserve IDENTITY ids  
DECLARE @idmapping TABLE (GeneratedID INT PRIMARY KEY,  
    NaturalID VARCHAR(20) NOT NULL UNIQUE);  
  
DECLARE @xml XML =  
N'<root>  
   <state>  
      <StateName>Florida</StateName>  
      <Abbr>FL</Abbr>  
      <Capital>Tallahassee</Capital>  
      <cities>  
         <city>  
            <city>Miami</city>  
            <population>470194</population>  
         </city>  
         <city>  
            <city>Orlando</city>  
            <population>285713</population>  
         </city>  
      </cities>  
   </state>  
   <state>  
      <StateName>Texas</StateName>  
      <Abbr>TX</Abbr>  
      <Capital>Austin</Capital>  
      <cities>  
         <city>  
            <city>Houston</city>  
            <population>2100263</population>  
         </city>  
         <city>  
            <city>Dallas</city>  
            <population>5560892</population>  
         </city>  
      </cities>  
   </state>  
</root>';  
-- DDL and sample data population, end  
  
;WITH rs AS   
(  
    SELECT stateName = p.value('(StateName/text())[1]', 'VARCHAR(30)'),  
           abbr = p.value('(Abbr/text())[1]', 'CHAR(2)'),  
           capital = p.value('(Capital/text())[1]', 'VARCHAR(30)')  
    FROM   @xml.nodes('/root/state') AS t(p)  
 )  
 MERGE #state AS o  
 USING rs ON 1 = 0  
 WHEN NOT MATCHED THEN  
    INSERT(stateName, abbr, capital)    
       VALUES(rs.stateName, rs.Abbr, rs.Capital)  
 OUTPUT inserted.stateID, rs.stateName   
 INTO @idmapping (GeneratedID, NaturalID);  
  
;WITH Details AS   
(  
    SELECT NaturalID = p.value('(StateName/text())[1]', 'VARCHAR(30)'),  
           city = c.value('(city/text())[1]', 'VARCHAR(30)'),  
           [population] = c.value('(population/text())[1]', 'INT')  
    FROM   @xml.nodes('/root/state') AS A(p) -- parent  
 CROSS APPLY A.p.nodes('cities/city') AS B(c) -- child  
)   
INSERT #city (stateID, city, [Population])  
SELECT m.GeneratedID, d.city, d.[Population]  
FROM   Details AS d  
 INNER JOIN @idmapping AS m ON d.NaturalID = m.NaturalID;  
  
-- test  
SELECT * FROM #state;  
SELECT * FROM @idmapping;  
SELECT * FROM #city;  

Answer 2

In general: The best way is to handle it entirely in your .NET application. Cause not every XML construct is easily transferred to a set of relations.

1) Only viable when having a concise and not too complex XSD.
2) Bulk copy is only necessary when operating with large data chunks. Use staging tables for the data to insert. Error handling should be handled in a defensive manner, thus you only bulk load correct data into your staging tables on the server. Then you run the inserts to your final tables inside a single transaction. Caveat: different approaches may be necessary in high load environments to reduce locking.

Answer 3

I'd say that the answer depends on the situation and what pattern that is used in the rest of the application. In the system I work with currently, we would certainly send the XML to the stored procedure.

But Stefan is right that it is better to shred client-side. SQL Server is a powerful relational engine, and shredding XML is not its core business.

Once you have the data in relational format, SqlBulkCopy is one option. A stored procedure with a table-valued parameter is another. SqlBulkCopy is faster, particularly for larger data sets, but also rawer I believe.

If you want all or nothing loaded, you need to bracket the operation with a transaction.