CODEWITHSUNDEEP
c#sql-serverxmlxmldocument
GP24
GP24

Reputation: 877

Insert XmlDocument representing FOR XML result in to database

The code below pulls data out of a table and gives you an XmlDocument to work with:

using (SqlCommand command = CreateCommand())
{
    command.CommandType = CommandType.Text;
    command.CommandText = "SELECT * FROM SOME_TABLE FOR XML AUTO, ELEMENTS, ROOT('SomeEntities')";

    var xmlReader = command.ExecuteXmlReader();
    var xmlDoc = new XmlDocument();

    xmlDoc.Load(xmlReader);
}

Giving you an XML representation of the data like this, for example:

<SomeEntities>
    <Entity>
        <SomeKey>123<SomeKey/>
        <SomeValue>TestString<SomeValue/>
    <Entity/>
<SomeEntities/>

Which is great, but is there a way to take the same XML representation of that same entire row, and insert it back in to the database?

MSDN says FOR XML will work with an INSERT but gives no example, and maybe I am using the wrong search terms but there does not seem to be much out there on this. This seems like something you would be able to do, right?

Any thoughts? Happy to provide more code or try ideas, just comment :)

Upvotes: 1

Views: 138

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

There is no automatic way to achieve this. As a matter of fact XML is very generic and can carry almost any kind of data in almost any structure and nestings. How should a tool know automatically what to do?

Some suggestions

1) You might create a schema (XSD) and create a DataSet from this. With a structurally fitting DataSet it is easy to "fill" it with your XML and then use the DataSet's features.

2) Create a stored procedure taking your XML as passed in parameter. This SP will read the data and insert them properly.

3) Write some code in C# to achieve what you want.

Upvotes: 1

Related Questions