Hello,
I'm trying to import a Xml file in a table.
It's working find, but when the data are inserted, my trigger on this
table doesn't work ?

I put my code below.

Best regards

Thank's

David


Xml file :
<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>
<RTAVIS>
<FILENAME>03-05-073.PDF</FILENAME>
<idvir>1030</idvir>
<code>9219142</code>
<mnt>4337,88 </mnt>
</RTAVIS>


Xsd file :
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >

<ElementType name="FILENAME" dt:type="string" />
<ElementType name="idvir" dt:type="string" />
<ElementType name="code" dt:type="string" />
<ElementType name="mnt" dt:type="string" />

<ElementType name="RTAVIS" sql:relation="ac_export_and_path">
<element type="FILENAME" sql:field="path" />
<element type="idvir" sql:field="numero_virement" />
<element type="code" sql:field="code_affilie" />
<element type="mnt" sql:field="montant_remise" />
</ElementType>

</Schema>

Vbs file:
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=*****;database=*****;uid=*****;pwd=*****"
objBL.ErrorLogFile = "C:\Temp\error.xml"
objBL.SchemaGen = True
objBL.CheckConstraints = True
objBL.KeepIdentity = False
objBL.BulkLoad = true
objBL.Execute "C:\Temp\9219142_MAPPING.xsd", "C:\Temp\9219142.xml"
Set objBL = Nothing


Trigger source code :

CREATE TRIGGER [dbo].[trg_ac_export_and_path] ON
dbo.ac_export_and_path
FOR INSERT
AS

DECLARE @id AS varchar(50)

SELECT @id = 'A'+convert(varchar, [id]) FROM inserted

INSERT INTO ac_path([id], [path]) SELECT @id, path FROM inserted

END

M6C (roca.david (AT) gmail (DOT) com) writes:
By default triggers don't fire when you bulk load. At least not regular
bulk load. I don't know if XML bulk load is any different. Regular
bulk load has an option to fire triggers. You may want to explorer
whether XML bulk load offers this too.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 11 mai, 00:23, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thank you, but i don't find any information about an option to fire
triggers.
I will continue to looking for more informations about XML bulk load
options.

An other question, to circumvent my problem;
Can i make an operation in the Xsd file ?
I want to add an constant character to an column.
Can i modify the Xsd file to look like the following:

Best Regards

Thank's

David

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >


<ElementType name="FILENAME" dt:type="string" />
<ElementType name="idvir" dt:type="string" />
<ElementType name="code" dt:type="string" />
<ElementType name="mnt" dt:type="string" />


<ElementType name="RTAVIS" sql:relation="ac_export_and_path">
<element type="FILENAME" sql:field="path" />
<element type="idvir"
sql:field="append("A",numero_virement)" />
<element type="code" sql:field="code_affilie" />
<element type="mnt" sql:field="montant_remise" />
</ElementType>


</Schema>

M6C (roca.david (AT) gmail (DOT) com) writes:
I looked in MSDN Library, and according to the topic "SQL Server XML Bulk
Load Object Model", there is indeed a FireTriggers property. This is for
SQLXML 4.0. I don't have much experience of SQLXML, but FireTriggers is not
listed under "What's New in SQL 4.0", so I guess it was there in 3.0 as
well.

That question definitely goes over my head! :-)

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On 13 mai, 18:25, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
In fact SQLXML 4.0 have a FileTriggers property. But i try this option
in SQLXML 3.0 and it's not available.
So i must upgrade my sql server to the 2005 version, to use it.
Thank you for your answers. Now i know the reason of this
comportement.