How to add the content of Xml files to a SQL Server database

Question

I'm trying to insert .xml data into a Sql Server database.

Hardcoded works ->

        string strSQL = @"
        DECLARE @input XML = '<Mitarbeiter><Mitarbeiter><ID>6000</ID><Vorname>Ulli</Vorname><Nachname>Unfassbar</Nachname><Gehalt>2000</Gehalt></Mitarbeiter></Mitarbeiter>' 

        INSERT INTO Mitarbeiter(ID, Vorname, Nachname, Gehalt)

        SELECT                          
        XEmp.value('(ID)[1]', 'int'),
        XEmp.value('(Vorname)[1]', 'varchar(50)'),
        XEmp.value('(Nachname)[1]', 'varchar(50)'),
        XEmp.value('(Gehalt)[1]', 'int')
        FROM 
        @input.nodes('/Mitarbeiter/Mitarbeiter') AS XTbl(XEmp)";

...but this should work with a .xml file. I tried to add the file path, but without success. Here is how I added the file path.

        string strSQL = @"

        DECLARE @input XML

        SELECT @xml = XTbl.XEmp

        FROM OPENROWSET( BULK 'C:\TestMail.xml', SINGLE_CLOB ) XTbl(XEmp)
        INSERT INTO Mitarbeiter(ID, Vorname, Nachname, Gehalt)

        SELECT
        XEmp.value('(ID)[1]', 'int'),
        XEmp.value('(Vorname)[1]', 'varchar(50)'),
        XEmp.value('(Nachname)[1]', 'varchar(50)'),
        XEmp.value('(Gehalt)[1]', 'int')

        FROM 
        @input.nodes('/Mitarbeiter/Mitarbeiter') AS XTbl(XEmp)";

Any idea? Thanks for any help on this in advance!

Nowhere in the second snippet. I`ve also tried to add the file path with a SET @input = 'file path'. — oldsport, Nov 18 '13 at 15:41
Assuming from your tags you're trying to do this in C# and it's not just a one-time, dump xml into db thing? — maplemale, Nov 18 '13 at 15:43
Is the SQL Server hosted on another system? And if so, remember that SQL Server can only see the filesystem of it's host server. It cannot see the client's filesystem unless they're the same system, or unless it's a share. — RBarryYoung, Nov 18 '13 at 15:47
In the end the xml files are received via mail(to the company hq). From there they must be inserted to a sql database. — oldsport, Nov 18 '13 at 16:15

score 1 · Accepted Answer · answered Nov 18 '13 at 15:38

1

After fixing the variable name it works on my server:

DECLARE @input XML

SELECT @input = XTbl.XEmp
FROM OPENROWSET(BULK 'C:\Testmail.xml', SINGLE_CLOB) XTbl(XEmp);

INSERT INTO Mitarbeiter (ID, Vorname, Nachname, Gehalt)
SELECT XEmp.value('(ID)[1]', 'int'), XEmp.value('(Vorname)[1]', 'varchar(50)'), XEmp.value('(Nachname)[1]', 'varchar(50)'), XEmp.value('(Gehalt)[1]', 'int')
FROM @input.nodes('/Mitarbeiter/Mitarbeiter') AS XTbl(XEmp);

answered Nov 18 '13 at 15:38

Twinkles

1,984
1
17
31

Are you going to execute this as a sql command in your c# code? Just curious what your final solution is and if this is a one time thing or not. – maplemale Nov 18 '13 at 15:48
Yes and yes so to say. It runs as sql command in c# code. The idea behind is that xml files are received via mail and than inserted into sql. – oldsport Nov 18 '13 at 16:10

score 1 · Answer 2 · edited May 23 '17 at 10:25

The standard approach here is:

Have a class with properties that can be populated from the xml.
Populate the class by deseralizing your xml to the object
Save the object to the database

I would recommend a store procedure as your method of inline sql is a bad practice which is vulnerable to sql injection attack, and difficult to trouble-shoot and maintain.

I'd recommend starting here and learning about serializing / deserializing: http://msdn.microsoft.com/en-us/library/system.xml.serialization.xmlserializer(v=vs.110).aspx

Also, you can quickly create a class that matches your xml if you have the XSD for the XML (reference: What is the difference between XML and XSD?). I think your XSD would look like this:

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Mitarbeiter">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Mitarbeiter">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:short" name="ID"/>
              <xs:element type="xs:string" name="Vorname"/>
              <xs:element type="xs:string" name="Nachname"/>
              <xs:element type="xs:short" name="Gehalt"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Save the above xsd to a .xsd file. Open a Visual Studio Command prompt and enter this command:

xsd your.xsd /classes

That will create a class for you and save a lot of typing. Use the xml to populate your class by deseralizing the xml file (reference msdn article above). Once your class is populated, create a save method in the class which inserts to the database via a stored procedure. Reference this:

Call a stored procedure with parameter in c#

You're welcome. I realize that just executing a SQL insert command from code is easier. However, just be aware that at most companies that are security conscious, I would not get away with that. But... beyond security reasons, if you get the chance/take the time to implement a more robust solution like I have above, there is the added benefit that you can do easily do other things with the data, not just save it. You can return it to a gui easily, you can delete it, update it etc. Also, if you ever have additional properties, it's as easy as adding a property. Good luck! — maplemale, Nov 18 '13 at 15:55

How to add the content of Xml files to a SQL Server database

2 Answers2