How Import XML file in a table with trigger.

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 :


03-05-073.PDF
1030
9219142
4337,88



Xsd file :

xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >















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

Re: How Import XML file in a table with trigger.

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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: How Import XML file in a table with trigger.

On 11 mai, 00:23, Erland Sommarskog 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/prodt=
echnol/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=E9c=E9dents -
>
> - Afficher le texte des messages pr=E9c=E9dents -

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


xmlns:dt=3D"urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql=3D"urn:schemas-microsoft-com:xml-sql" >










sql:field=3D"append("A",numero_virement)" />

Re: How Import XML file in a table with trigger.

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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: How Import XML file in a table with trigger.

On 13 mai, 18:25, Erland Sommarskog 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.