Inserting XML with SSIS - VERY URGENT!!!

Hello everybody,

I have a problem. I need to insert an unknown number of xml files in a database (all files are always in the same folder), in different tables, each file has the same name that the corresponding table. For example:

Files Tables

user.xml user

purchase.xml purchase

...and so

but the number of files is not always the same, I mean, it can be 6 one day and only 4 the next day.
Can I insert the data in the xml files into the tables with a Foreach Loop Container or any other way? If it's possible, how?

Thanks in advance for your help,

Radamante71

You might want to post your question at SQL Server Integration Services forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1.

Inserting XML values

" Insert Into...Value" seems to be the command for inserting new data into an XML Doc. I have also seen people using "Insert...Value" without the "Into" keyword. Is there a difference between "Insert" and "Insert Into"?

There is no difference. 'INTO' is optional keyword for INSERT ... VALUES() statement. It's not only for insert xml value, but for all other sql types.|||thank you :-)

Inserting XMl string into table.

Hi,

I want the value of a field as an XML string.

Ex: <student1 name ="df" age="16"/>

<student2 name ="gfdg" age="21"/>

<student3 name ="ddddf" age="11"/>

Please let me know whether the normal insert command can be used to insert this XML string

I have done with normal insert Command like

Insert INTO listtable list_id, listDetails VALUES 1, '<student1 name ="df" age="16"/>

<student2 name ="gfdg" age="21"/>

<student3 name ="ddddf" age="11"/>'

Here, the XML string is inserted into 2nd column.

Is this the way to do?

Because, when i read the string back, i am getting the characters &lt; etc. in place of "<", ">" etc.

How the XML string column is selected back to get the correct XML string without junk characters?

Please help me out.

I am not sure what goes wrong but you have not shown how you read out the data exactly.

The following is a sample that creates the table and inserts your sample data and then reads it out with a simple SELECT FROM, that works fine for me with SQL Server 2005 Management Studio Express, the angle brackets <> are certainly not escaped:

Code Snippet

CREATE TABLE #listtable (

list_id int,

listDetails xml

);

GO

INSERT INTO #listtable (list_id, listDetails)

VALUES(1, '<student1 name ="df" age="16"/>

<student2 name ="gfdg" age="21"/>

<student3 name ="ddddf" age="11"/>');

SELECT list_id, listDetails FROM #listtable;

inserting XML into msSQL

Hello everyone,
I have a large file about 75,000 records each with about 3 elements and 1
variable.
I need to know how to insert this entire file into an existing table, or new
table or anything as long as it is moved from the XML to the msSQL.
I read into OPENXML and all that in books online, but im having a large
amount of difficulty, seeing as i need to have the data directly in the
query.. is there a way to reference a file or something..
Thanks.The SQL Server Web Services ToolKit has a nice SQLXML BulkInsert provider
that you may find helpful.
058c0bfd7de&DisplayLang=en" target="_blank">http://www.microsoft.com/downloads/...&DisplayLang=en
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Adam" <adoeler@.sharklogic.com> wrote in message
news:syAOc.202$cE5.1830@.news20.bellglobal.com...
> Hello everyone,
> I have a large file about 75,000 records each with about 3 elements and 1
> variable.
> I need to know how to insert this entire file into an existing table, or
new
> table or anything as long as it is moved from the XML to the msSQL.
> I read into OPENXML and all that in books online, but im having a large
> amount of difficulty, seeing as i need to have the data directly in the
> query.. is there a way to reference a file or something..
> Thanks.
>|||Adam
declare @.list varchar(8000)
declare @.hdoc int
set @.list='<Northwind..Orders OrderId="10643"
CustomerId="ALFKI"/><Northwind..Orders OrderId="10692" CustomerId="ALFKI"/>'
select @.list='<Root>'+ char(10)+@.list
select @.List = @.List + char(10)+'</Root>'
exec sp_xml_preparedocument @.hdoc output, @.List
select OrderId,CustomerId
from openxml (@.hdoc, '/Root/Northwind..Orders', 1)
with (OrderId int,
CustomerId varchar(10)
)
exec sp_xml_removedocument @.hdoc
"Adam" <adoeler@.sharklogic.com> wrote in message
news:syAOc.202$cE5.1830@.news20.bellglobal.com...
> Hello everyone,
> I have a large file about 75,000 records each with about 3 elements and 1
> variable.
> I need to know how to insert this entire file into an existing table, or
new
> table or anything as long as it is moved from the XML to the msSQL.
> I read into OPENXML and all that in books online, but im having a large
> amount of difficulty, seeing as i need to have the data directly in the
> query.. is there a way to reference a file or something..
> Thanks.
>|||the problem with inserting that data into the query, is there is more
than 8000 characters in the entire thing, it would take ages to go and
select 8000 at a time. I need something like navicat that works a little
more solid. navicat seems like it is skipping fields... its all a big
pain in my XXX. hah.
if anyone can help. please do..
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

inserting XML into msSQL

Hello everyone,
I have a large file about 75,000 records each with about 3 elements and 1
variable.
I need to know how to insert this entire file into an existing table, or new
table or anything as long as it is moved from the XML to the msSQL.
I read into OPENXML and all that in books online, but im having a large
amount of difficulty, seeing as i need to have the data directly in the
query.. is there a way to reference a file or something..
Thanks.The SQL Server Web Services ToolKit has a nice SQLXML BulkInsert provider
that you may find helpful.
http://www.microsoft.com/downloads/details.aspx?FamilyID=ca1cc72b-6390-4260-b208-2058c0bfd7de&DisplayLang=en
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Adam" <adoeler@.sharklogic.com> wrote in message
news:syAOc.202$cE5.1830@.news20.bellglobal.com...
> Hello everyone,
> I have a large file about 75,000 records each with about 3 elements and 1
> variable.
> I need to know how to insert this entire file into an existing table, or
new
> table or anything as long as it is moved from the XML to the msSQL.
> I read into OPENXML and all that in books online, but im having a large
> amount of difficulty, seeing as i need to have the data directly in the
> query.. is there a way to reference a file or something..
> Thanks.
>|||Adam
declare @.list varchar(8000)
declare @.hdoc int
set @.list='<Northwind..Orders OrderId="10643"
CustomerId="ALFKI"/><Northwind..Orders OrderId="10692" CustomerId="ALFKI"/>'
select @.list='<Root>'+ char(10)+@.list
select @.List = @.List + char(10)+'</Root>'
exec sp_xml_preparedocument @.hdoc output, @.List
select OrderId,CustomerId
from openxml (@.hdoc, '/Root/Northwind..Orders', 1)
with (OrderId int,
CustomerId varchar(10)
)
exec sp_xml_removedocument @.hdoc
"Adam" <adoeler@.sharklogic.com> wrote in message
news:syAOc.202$cE5.1830@.news20.bellglobal.com...
> Hello everyone,
> I have a large file about 75,000 records each with about 3 elements and 1
> variable.
> I need to know how to insert this entire file into an existing table, or
new
> table or anything as long as it is moved from the XML to the msSQL.
> I read into OPENXML and all that in books online, but im having a large
> amount of difficulty, seeing as i need to have the data directly in the
> query.. is there a way to reference a file or something..
> Thanks.
>

inserting XML into msSQL

Hello everyone,
I have a large file about 75,000 records each with about 3 elements and 1
variable.
I need to know how to insert this entire file into an existing table, or new
table or anything as long as it is moved from the XML to the msSQL.
I read into OPENXML and all that in books online, but im having a large
amount of difficulty, seeing as i need to have the data directly in the
query.. is there a way to reference a file or something..
Thanks.
The SQL Server Web Services ToolKit has a nice SQLXML BulkInsert provider
that you may find helpful.
http://www.microsoft.com/downloads/d...DisplayLang=en
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Adam" <adoeler@.sharklogic.com> wrote in message
news:syAOc.202$cE5.1830@.news20.bellglobal.com...
> Hello everyone,
> I have a large file about 75,000 records each with about 3 elements and 1
> variable.
> I need to know how to insert this entire file into an existing table, or
new
> table or anything as long as it is moved from the XML to the msSQL.
> I read into OPENXML and all that in books online, but im having a large
> amount of difficulty, seeing as i need to have the data directly in the
> query.. is there a way to reference a file or something..
> Thanks.
>
|||Adam
declare @.list varchar(8000)
declare @.hdoc int
set @.list='<Northwind..Orders OrderId="10643"
CustomerId="ALFKI"/><Northwind..Orders OrderId="10692" CustomerId="ALFKI"/>'
select @.list='<Root>'+ char(10)+@.list
select @.List = @.List + char(10)+'</Root>'
exec sp_xml_preparedocument @.hdoc output, @.List
select OrderId,CustomerId
from openxml (@.hdoc, '/Root/Northwind..Orders', 1)
with (OrderId int,
CustomerId varchar(10)
)
exec sp_xml_removedocument @.hdoc
"Adam" <adoeler@.sharklogic.com> wrote in message
news:syAOc.202$cE5.1830@.news20.bellglobal.com...
> Hello everyone,
> I have a large file about 75,000 records each with about 3 elements and 1
> variable.
> I need to know how to insert this entire file into an existing table, or
new
> table or anything as long as it is moved from the XML to the msSQL.
> I read into OPENXML and all that in books online, but im having a large
> amount of difficulty, seeing as i need to have the data directly in the
> query.. is there a way to reference a file or something..
> Thanks.
>
|||the problem with inserting that data into the query, is there is more
than 8000 characters in the entire thing, it would take ages to go and
select 8000 at a time. I need something like navicat that works a little
more solid. navicat seems like it is skipping fields... its all a big
pain in my XXX. hah.
if anyone can help. please do..
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

inserting xml data

Hi,
Is there any way to insert the output of xml_auto into a table

for eg:

select * from categories for xml auto

i need the output of the abouve query to be inserted into another table
the destination table has one column,thomson (saintthomson@.yahoo.com) writes:
> Is there any way to insert the output of xml_auto into a table
> for eg:
> select * from categories for xml auto
> i need the output of the abouve query to be inserted into another table
> the destination table has one column,

I think the only way you can do this in SQL 2000 is to use OPENQUERY:

INSERT tbl (col)
SELECT * FROM OPENQUERY (LOOPBACK,
'SELECT * FROM categories FROM XML AUTO')

Here LOOPBACK is a linked server back to your own, and here is a real
funny thing: you must set it up to use MSDASQL, that is the OLE DB over
ODBC provider! If you use SQLOLEDB which is the recommended provider,
you will get binary data back.

But when I did a quick test, the result was not entirely acceptable, since
the XML string was split up over six rows.

In the next version of SQL Server, SQL 2005, currency in beta, there
are significant enhancements in XML support, including a specific xml
datatype, and you can do this easily.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||xmlbulkload no good?

Inserting with Yukon's modify insert statement

I have seen many examples of inserting Xml with a literal Xml chuck.
Like this:
UPDATE docs
SET xbook.modify(
'insert <chapter num="2">
<title>Introduction</title>
</chapter>
after (/book//chapter[@.num=1])[1]')
GO
But I would like to insert an XML variable, not just a scalar.
Like this:
DECLARE @.Chunk Xml
SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
UPDATE docs
SET xbook.modify(
'insert sql:variable("@.Chunk")
after (/book//chapter[@.num=1])[1]')
GO
Thanks in advance. Mark
This is (unfortunately) not supported since sql:column/sql:variable is not
allowed on the XML datatype.
I tried to get us to support the scenario below directly, but it got
postponed.
The solutions that are available are:
1. Use dynamic SQL:
exec('UPDATE docs
SET xbook.modify(
''insert ' + CAST(@.Chunk as nvarchar(max)) +
'after (/book//chapter[@.num=1])[1]'')'
2. Use XQuery and FOR XML to create the new XML and replace the old one.
I am not happy about this either...
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:uSWyyQ5ZFHA.1148@.tk2msftngp13.phx.gbl...
>I have seen many examples of inserting Xml with a literal Xml chuck.
> Like this:
> UPDATE docs
> SET xbook.modify(
> 'insert <chapter num="2">
> <title>Introduction</title>
> </chapter>
> after (/book//chapter[@.num=1])[1]')
> GO
>
> But I would like to insert an XML variable, not just a scalar.
> Like this:
> DECLARE @.Chunk Xml
> SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
> UPDATE docs
> SET xbook.modify(
> 'insert sql:variable("@.Chunk")
> after (/book//chapter[@.num=1])[1]')
> GO
>
> Thanks in advance. Mark
>
>
|||> This is (unfortunately) not supported since sql:column/sql:variable is not
Oh well...
Thanks, Mark Bosley
Let me say, while I can that the level to which Xml is now a first class
citizen of T-SQL is pretty impressive.
If Yukon had only CTE's
OR
XQuery support
OR
'FOR XML PATH', it would seem like a major advance. Having them all is
pretty overwhelming. It is like the jump for me from flat files to SQL
Server 6.5 ten years ago. Great work!
|||Thanks for the flowers :-).
But there is still lots of more work ahead and your feedback (you
specifically and in general the readership of the newsgroup) will help us
prioritize the work.
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:ez0%230j8ZFHA.3068@.TK2MSFTNGP12.phx.gbl...
> Oh well...
> Thanks, Mark Bosley
> Let me say, while I can that the level to which Xml is now a first class
> citizen of T-SQL is pretty impressive.
> If Yukon had only CTE's
> OR
> XQuery support
> OR
> 'FOR XML PATH', it would seem like a major advance. Having them all is
> pretty overwhelming. It is like the jump for me from flat files to SQL
> Server 6.5 ten years ago. Great work!
>

Inserting with Yukon's modify insert statement

I have seen many examples of inserting Xml with a literal Xml chuck.
Like this:
UPDATE docs
SET xbook.modify(
'insert <chapter num="2">
<title>Introduction</title>
</chapter>
after (/book//chapter[@.num=1])[1]')
GO
But I would like to insert an XML variable, not just a scalar.
Like this:
DECLARE @.Chunk Xml
SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
UPDATE docs
SET xbook.modify(
'insert sql:variable("@.Chunk")
after (/book//chapter[@.num=1])[1]')
GO
Thanks in advance. MarkThis is (unfortunately) not supported since sql:column/sql:variable is not
allowed on the XML datatype.
I tried to get us to support the scenario below directly, but it got
postponed.
The solutions that are available are:
1. Use dynamic SQL:
exec('UPDATE docs
SET xbook.modify(
''insert ' + CAST(@.Chunk as nvarchar(max)) +
'after (/book//chapter[@.num=1])[1]'')'
2. Use XQuery and FOR XML to create the new XML and replace the old one.
I am not happy about this either...
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:uSWyyQ5ZFHA.1148@.tk2msftngp13.phx.gbl...
>I have seen many examples of inserting Xml with a literal Xml chuck.
> Like this:
> UPDATE docs
> SET xbook.modify(
> 'insert <chapter num="2">
> <title>Introduction</title>
> </chapter>
> after (/book//chapter[@.num=1])[1]')
> GO
>
> But I would like to insert an XML variable, not just a scalar.
> Like this:
> DECLARE @.Chunk Xml
> SET @.Chunk = '<chapter num="2"><title>Introduction</title></chapter>'
> UPDATE docs
> SET xbook.modify(
> 'insert sql:variable("@.Chunk")
> after (/book//chapter[@.num=1])[1]')
> GO
>
> Thanks in advance. Mark
>
>|||> This is (unfortunately) not supported since sql:column/sql:variable is not
Oh well...
Thanks, Mark Bosley
Let me say, while I can that the level to which Xml is now a first class
citizen of T-SQL is pretty impressive.
If Yukon had only CTE's
OR
XQuery support
OR
'FOR XML PATH', it would seem like a major advance. Having them all is
pretty overwhelming. It is like the jump for me from flat files to SQL
Server 6.5 ten years ago. Great work!|||Thanks for the flowers :-).
But there is still lots of more work ahead and your feedback (you
specifically and in general the readership of the newsgroup) will help us
prioritize the work.
Best regards
Michael
"Mark Bosley" <mark.nspam@.lightcc.com> wrote in message
news:ez0%230j8ZFHA.3068@.TK2MSFTNGP12.phx.gbl...
> Oh well...
> Thanks, Mark Bosley
> Let me say, while I can that the level to which Xml is now a first class
> citizen of T-SQL is pretty impressive.
> If Yukon had only CTE's
> OR
> XQuery support
> OR
> 'FOR XML PATH', it would seem like a major advance. Having them all is
> pretty overwhelming. It is like the jump for me from flat files to SQL
> Server 6.5 ten years ago. Great work!
>

Inserting with XML files - VERY URGENT!!!

Hello everybody,

I have a problem. I need to insert an unknown number of xml files in a database (all files are always in the same folder), in different tables, each file has the same name that the corresponding table. For example:

Files Tables

user.xml user

purchase.xml purchase

...and so

but the number of files is not always the same, I mean, it can be 6 one day and only 4 the next day.
Can I insert the data in the xml files into the tables with a Foreach Loop Container or any other way? If it's possible, how?

Thanks in advance for your help,

Radamante71

Create a ForEach Loop task to read the files from the directory and assign the name to a variable. Then add a dataflow task to the foreach loop and in your XML source file, select "XML File from variable", then select the variable name.|||

Tom suggestion will work if all file/tables had the same structure, which I doubt. To the original poster: I think what you want to accomplish is not possible. Basically you are asking to create the source-to-target data mapping on the fly and that is something you cannot do in a SSIS package (at least to my knowledge).

Rafael Salas

|||

Rafael,

One question:

Do you speak spanish? Because if you do, we can continue in spanish. I'm spanish and I think that I can explain you my problem in a better way if I do it in my language.

Thanks

|||You are correct. I don't see any way to use a variable XSD file.

However, you could use the "Inline Schema" in the file, that would work.|||

Radamante,

Yes, I do speak; but I don't think it would be a good idea to continue this thread in Spanish; that could irritate some non-Spanish speaker folks in this forum. If you want, send me an e-mail with your problem (be sure to de-spam my email address before) to see if I can help you.

Rafael Salas