Hi Robert
Thank you very much for your brilliant
project.
I just want to ask is there a way (like
SQLiteFunction ) to support xml crud operation? Like in ms sql 2000 there was a
function called openxml which return a rowset that can be combine in any
querries. Because if delete/update hundred of records it is very slow using
conventional insert/delete. Your code on FastInsertMany sample is good and it is really fast. The below
code are sample sql script that I use on ms sql database with crud operation.
Thanks,
breeze
Sample of code using open xml in ms
sql 2000
Delete
/*--SAMPLE DATA--
--======================================================
SET
@xmlData = '<?xml version="1.0" encoding="utf-8"?>
<root>
<Data
Id="147" />
<Data
Id="146" />
<Data
Id="148" />
<Data
Id="145" />
</root>'
--=====================================================*/
AS
DECLARE
@idoc INT
EXEC
sp_xml_preparedocument @idoc OUTPUT, @xmlData
DELETE
Dbo.Authors
FROM
dbo.Authors
INNER JOIN
(
SELECT
XmlTable.au_id
FROM
OPENXML(@idoc,
'root/Data')
WITH
(
au_id INT '@Id'
) AS XmlTable
)tempTable
ON dbo.Authors.ShiftId =
tempTable.ShiftId
EXEC
sp_xml_removedocument @idoc
--update
--SAMPLE
DATA--
/*=================================
SET @xml =
'<?xml
version="1.0"?>
<root>
<data au_id="101"
au_lname="Testing Name" au_fname="Testing Name" />
<data au_id="102"
au_lname="Testing Name" au_fname="Testing Name2" />
<data au_id="103"
au_lname="Testing Name" au_fname="Testing Name4" />
</root>'
==================================*/
AS
DECLARE
@idoc INT
EXEC
sp_xml_preparedocument @idoc OUTPUT, @xml
UPDATE
dbo.Authors
SET
au_lname = xmlDoc.au_lname,
au_fname = xmlDoc.au_fname
FROM
(
SELECT
au_id,
au_lname,
au_fname
FROM
OpenXML(@idoc,'root/data')
WITH
(
au_id INT,
au_lname VARCHAR(100),
au_fname VARCHAR(20)
)
)
xmlDoc
WHERE dbo.Authors.au_id = xmlDoc.au_id
EXEC
sp_xml_removedocument @idoc
--insert
/*=================================
SET @xml =
'<?xml
version="1.0"?>
<root>
<data
au_lname="Testing Name" au_fname="Testing Name"
/>
<data
au_lname="Testing Name" au_fname="Testing Name2"
/>
<data
au_lname="Testing Name" au_fname="Testing Name4"
/>
</root>'
==================================*/
DECLARE
@idoc INT
EXEC
sp_xml_preparedocument @idoc OUTPUT, @xml
INSERT INTO
dbo.Authors
(
au_lname,
au_fname
)
SELECT
au_lname,
au_fname
FROM OpenXML(@idoc,'root/data')
WITH
(
au_lname VARCHAR(100),
au_fname VARCHAR(20)
)
EXEC
sp_xml_removedocument @idoc