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@gmail.com) writes:
> 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 ?
>...
> 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

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@sommarskog.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...@sommarskog.se> wrote:
> 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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Masquer le texte des messages précédents -
>
> - Afficher le texte des messages précédents -

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@gmail.com) writes:
> 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.

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.

> 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:

That question definitely goes over my head! :-)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.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...@sommarskog.se> wrote:
> M6C (roca.da...@gmail.com) writes:
> > 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.
>
> I looked in MSDN Library, and according to the topic "SQL Server XML Bulk
> Load Object Model", there is indeed aFireTriggersproperty. This is forSQLXML4.0. I don't have much experience ofSQLXML, butFireTriggersis not
> listed under "What's New in SQL 4.0", so I guess it was there in 3.0 as
> well.
>

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.