Sql-server – How to insert multiple records using XML in SQL server 2008

sql-server-2008xml

i use SQL server 2008 in my web application back end. Apparently i iterate through all the records from the C# code whenever there is a multiple insertion scenario. i have never tried the multple insertion using XML. And i think after reading many blogs about XML manipulation using SQL server 2008 the process is pretty tideous ..
So my question is..

Is Insertion via XML much efficient than the traditional insertion?
Is the a generic way in which i can serialize a class in C# –> Manipulate XML in SQL –>Insert also Read data as XML–> Deserialize XML to usual object?

Best Answer

The whole "inserting multiple records with XML" has pretty much been superseded by table parameters in SQL Server 2008.

And mentioned in Erland Erland Sommarskog's Arrays and Lists in SQL Server 2008: the definitive article on this subject.

.net has better XML handling than the SQLXML implementation too.

Related Solutions

Sql-server – Read XML file in SQL Server 2008

This parse node based XML. It's different to read attributes but it isn't as common

I had this lying around as a demo with 3 slightly different XPath queries

DECLARE @foo XML

SELECT @foo = N'
<harrys>
    <harry>
        <fish>0.015000000000</fish>
        <bicycle>2008-10-31T00:00:00+01:00</bicycle>
        <foo>ü</foo>
    </harry>
    <harry>
        <fish>0.025000000000</fish>
        <bicycle>2008-08-31T00:00:00+01:00</bicycle>
        <foo>ä</foo>
    </harry>
</harrys>
'

SELECT
    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(y.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('/*') x(item)
    CROSS APPLY
    x.item.nodes('./*') AS y(item)

SELECT
    CAST(CAST(x.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(x.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(x.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('harrys/harry') x(item)

SELECT
    CAST(CAST(y.item.query('data(fish)') AS varchar(30)) AS float),
    CAST(LEFT(CAST(y.item.query('data(bicycle)') AS char(25)), 10) AS smalldatetime),
    CAST(y.item.query('data(foo)') AS varchar(25))
FROM
    @foo.nodes('/harrys') x(item)
    CROSS APPLY
    x.item.nodes('./harry') AS y(item)

Sql-server – SQL Server 2008 Maintenance Plan Failure

You have "SET ANSI_WARNINGS OFF" when running the code as T_SQL

When running the job this setting must be different.

Best Answer

Related Solutions

Sql-server – Read XML file in SQL Server 2008

Sql-server – SQL Server 2008 Maintenance Plan Failure

Related Question