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/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!