question

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 asked ·

XML data into SQL Tables

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



sql-server-generalsql-server-transact-sql
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Without knowing the following:

  • XML structure

  • DB tables structure

  • XML size

  • SQL Server version (SELECT @@VERSION;)

It is impossible to suggest anything.

Please edit your post and add all of the above.




0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered ·

@kasimmohamed-6993,

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;

· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks YitzhakKhabinsky-0887 .
Exactly this is what i want.

0 Votes 0 ·

Why did you mark this as answer?

You asked explicitly for the general, "best way". This rules out shredding XML in T-SQL.
It's not, that it is sometimes a viable way for certain problems. But not in general, which is what you asked for.

0 Votes 0 ·
StefanHoffmann avatar image
0 Votes"
StefanHoffmann answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.