Posted by M6C on 05/09/07 22:55

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

• Next in forum: DBCC INPUTBUFFER results into table
• Prev in forum: Re: Output result of query to existing file
• Thread view: How Import XML file in a table with trigger.

[Reply to this message]