Hi...
I work with xml files, I use a foreach loop for process them.
I want to save the xml data into a table...

I can use this query:

DECLARE @xml XML
SELECT @xml =CONVERT(XML, bulkcolumn, 2)
FROM OPENROWSET(BULK 'c:\temp\TLGBAA00000000FE-FEA8780.xml', SINGLE_NCLOB)
AS x
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
insert into tbl_dati
select ID_TP,ID_TD,ID_CPT,CONVERT(datetime, '20'+SUBSTRING(dati_tmst, 7, 2)
+'-'+SUBSTRING(dati_tmst, 4, 2) +'-'+LEFT(dati_tmst, 2)+ '
'+sUBSTRING(dati_tmst, len(dati_tmst)-4, 2)+':'+RIGHT(dati_tmst,
2)+':00.000',121),DATI_VAL
from OpenXml(@idoc, '/Periferica/Dati')
with (ID_TP NCHAR(10) '../ID_TP', ID_TD NCHAR(10) 'ID_TD',ID_CPT NCHAR(10)
'../ID_CPT', DATI_TMST nvarchar(50) 'DATI_TMST',datI_VAL decimal(18,2)
'DATI_VAL')


That are my questions:
1- how I can pass the file name with a variable???
2- what item I must to use for process this query???
3- is there an another way to insert the xml data into table???

thanks
anja

Hi,

Anja wrote:
this is the way I did it in a project quite a while ago:

Inside the foreach loop I have loaded the xml file into a
variable using the xml task (xslt) with an identity trans-
formation and storing the result in a variable.

In the data flow task I have used the xml source with "xml
data from variable" (I can't remember why I haven't chosen
"xml file from variable", but most probably I have made
some additional xslt transformations before) and have also
specified a schema (xsd). The xml source has several out-
puts which you can use for filling your tables.

hth,
Gerald

Hi,

Anja wrote:
this is the way I did it in a project quite a while ago:

Inside the foreach loop I have loaded the xml file into a
variable using the xml task (xslt) with an identity trans-
formation and storing the result in a variable.

In the data flow task I have used the xml source with "xml
data from variable" (I can't remember why I haven't chosen
"xml file from variable", but most probably I have made
some additional xslt transformations before) and have also
specified a schema (xsd). The xml source has several out-
puts which you can use for filling your tables.

hth,
Gerald

Hi,

Anja wrote:
this is the way I did it in a project quite a while ago:

Inside the foreach loop I have loaded the xml file into a
variable using the xml task (xslt) with an identity trans-
formation and storing the result in a variable.

In the data flow task I have used the xml source with "xml
data from variable" (I can't remember why I haven't chosen
"xml file from variable", but most probably I have made
some additional xslt transformations before) and have also
specified a schema (xsd). The xml source has several out-
puts which you can use for filling your tables.

hth,
Gerald

Hi,

Anja wrote:
this is the way I did it in a project quite a while ago:

Inside the foreach loop I have loaded the xml file into a
variable using the xml task (xslt) with an identity trans-
formation and storing the result in a variable.

In the data flow task I have used the xml source with "xml
data from variable" (I can't remember why I haven't chosen
"xml file from variable", but most probably I have made
some additional xslt transformations before) and have also
specified a schema (xsd). The xml source has several out-
puts which you can use for filling your tables.

hth,
Gerald

Hi,

Anja wrote:
this is the way I did it in a project quite a while ago:

Inside the foreach loop I have loaded the xml file into a
variable using the xml task (xslt) with an identity trans-
formation and storing the result in a variable.

In the data flow task I have used the xml source with "xml
data from variable" (I can't remember why I haven't chosen
"xml file from variable", but most probably I have made
some additional xslt transformations before) and have also
specified a schema (xsd). The xml source has several out-
puts which you can use for filling your tables.

hth,
Gerald

Hi,

Anja wrote:
this is the way I did it in a project quite a while ago:

Inside the foreach loop I have loaded the xml file into a
variable using the xml task (xslt) with an identity trans-
formation and storing the result in a variable.

In the data flow task I have used the xml source with "xml
data from variable" (I can't remember why I haven't chosen
"xml file from variable", but most probably I have made
some additional xslt transformations before) and have also
specified a schema (xsd). The xml source has several out-
puts which you can use for filling your tables.

hth,
Gerald

Hi,

Anja wrote:
this is the way I did it in a project quite a while ago:

Inside the foreach loop I have loaded the xml file into a
variable using the xml task (xslt) with an identity trans-
formation and storing the result in a variable.

In the data flow task I have used the xml source with "xml
data from variable" (I can't remember why I haven't chosen
"xml file from variable", but most probably I have made
some additional xslt transformations before) and have also
specified a schema (xsd). The xml source has several out-
puts which you can use for filling your tables.

hth,
Gerald

Hi,

Anja wrote:
this is the way I did it in a project quite a while ago:

Inside the foreach loop I have loaded the xml file into a
variable using the xml task (xslt) with an identity trans-
formation and storing the result in a variable.

In the data flow task I have used the xml source with "xml
data from variable" (I can't remember why I haven't chosen
"xml file from variable", but most probably I have made
some additional xslt transformations before) and have also
specified a schema (xsd). The xml source has several out-
puts which you can use for filling your tables.

hth,
Gerald