Manuela Importing File Xml into table with Sql Server 2005

Hi All,
I'm trying to import XML file into SQL without any success.
I use Sql Server 2005 and when I execute this code:
DECLARE @.xmlDoc xml;
SET @.xmlDoc = (
SELECT Q.BulkColumn
FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q);
I obtain this error:
Syntax non corrected in proximity of the word key "BULK". Why?
Moreover,I enable openrowset on window Sql Server 2005 Surface Area
Configuration.
I don't know where is the problem. Please help!!!
How about something like this?
DECLARE @.xmlDoc xml;
SELECT @.xmlDoc = BulkColumn
FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
Does this work?
Denis Ruckebusch
http://blogs.msdn.com/denisruc
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Manuela" <manuela@.discussions.microsoft.com> wrote in message
news:466B8045-B4C5-43E3-8C7D-48465DB5D525@.microsoft.com...
> Hi All,
> I'm trying to import XML file into SQL without any success.
> I use Sql Server 2005 and when I execute this code:
> DECLARE @.xmlDoc xml;
> SET @.xmlDoc = (
> SELECT Q.BulkColumn
> FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q);
> I obtain this error:
> Syntax non corrected in proximity of the word key "BULK". Why?
> Moreover,I enable openrowset on window Sql Server 2005 Surface Area
> Configuration.
> I don't know where is the problem. Please help!!!
>
|||Hello Denis,
Excuse me, I don't understand I what to make.
File Xml is Orders.xml:
<root>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">
<Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
</Customers>
</root>
and my schema is Orders.xsd:
<root>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">
<Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
</Customers>
</root>
I must use the schema?
I ask you excuse, but I have not just understood what to make.
Aspect one your answer. Thanks Denis.
This has the same error:
DECLARE @.xmlDoc xml;
SELECT @.xmlDoc = BulkColumn
FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
Importing File Xml into table with Sql Server 2005
"Denis Ruckebusch [MSFT]" wrote:

> How about something like this?
> DECLARE @.xmlDoc xml;
> SELECT @.xmlDoc = BulkColumn
> FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
> Does this work?
>
> Denis Ruckebusch
> http://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "Manuela" <manuela@.discussions.microsoft.com> wrote in message
> news:466B8045-B4C5-43E3-8C7D-48465DB5D525@.microsoft.com...
>
>
|||Hi Denis,
I have a 1GB XML file that is provided to us by a vendor and I need t
load it into our SQL 2005 database. Do you have any recommendation on
which method to use in order to accomplish this task? I am not sure
that the simple BULK load can handle it. Thanks,
Adi
On Jan 23, 3:59 pm, "Denis Ruckebusch [MSFT]"
<denis...@.online.microsoft.com> wrote:[vbcol=seagreen]
> How about something like this?
> DECLARE @.xmlDoc xml;
> SELECT @.xmlDoc = BulkColumn
> FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
> Does this work?
> Denis Ruckebuschhttp://blogs.msdn.com/denisruc
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm
> "Manuela" <manu...@.discussions.microsoft.com> wrote in messagenews:466B8045-B4C5-43E3-8C7D-48465DB5D525@.microsoft.com...
>
|||How do you want to load it? Into an XML data type column? Or shred it into
relational form?
Also what are you planning on doing to the document later?
Best regards
Michael
"Adi" <adisaric@.hotmail.com> wrote in message
news:1169661458.745857.233320@.a75g2000cwd.googlegr oups.com...
> Hi Denis,
> I have a 1GB XML file that is provided to us by a vendor and I need t
> load it into our SQL 2005 database. Do you have any recommendation on
> which method to use in order to accomplish this task? I am not sure
> that the simple BULK load can handle it. Thanks,
> Adi
> On Jan 23, 3:59 pm, "Denis Ruckebusch [MSFT]"
> <denis...@.online.microsoft.com> wrote:
>
|||The XML format should not have anything to do with your syntax error.
Are you sure you are using SQL Server 2005?
If so, you may also want to make sure that you use the same matching single
quotes for the file name argument:
FROM OPENROWSET(BULK 'C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
Best regards
Michael
"Manuela" <manuela@.discussions.microsoft.com> wrote in message
news:6A48B9ED-0949-4C53-BF54-3D1D36D84C35@.microsoft.com...[vbcol=seagreen]
> Hello Denis,
> Excuse me, I don't understand I what to make.
> File Xml is Orders.xml:
> <root>
> <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
> <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
> <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
> </Customers>
> <Customers CustomerID="XYZBB" ContactName="Steve"
> CompanyName="Company2">
> <Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
> </Customers>
> </root>
> and my schema is Orders.xsd:
> <root>
> <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
> <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
> <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
> </Customers>
> <Customers CustomerID="XYZBB" ContactName="Steve"
> CompanyName="Company2">
> <Orders CustomerID="XYZBB" OrderDate="2003-06-12T12:00:00"/>
> </Customers>
> </root>
> I must use the schema?
> I ask you excuse, but I have not just understood what to make.
> Aspect one your answer. Thanks Denis.
> This has the same error:
> DECLARE @.xmlDoc xml;
> SELECT @.xmlDoc = BulkColumn
> FROM OPENROWSET(BULK ''C:\Orders.xml', SINGLE_CLOB) AS Q(BulkColumn)
>
> --
> Importing File Xml into table with Sql Server 2005
>
> "Denis Ruckebusch [MSFT]" wrote:
|||I am looking to shred it into relational form. Planning to populate
several tables based on the data contained in the file. Once that is
done the xml file can be either deleted or archived. Thanks,
Adi
On Jan 24, 4:51 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> How do you want to load it? Into anXMLdata type column? Or shred it into
> relational form?
> Also what are you planning on doing to the document later?
> Best regards
> Michael
> "Adi" <adisa...@.hotmail.com> wrote in messagenews:1169661458.745857.233320@.a75g2000cwd.g ooglegroups.com...
>
>
>
>
>
|||In that case I would recommend looking into defining an annotated XML Schema
for the data and using the SQLXML XML Bulkload object.
Best regards
Michael
"Adi" <adisaric@.hotmail.com> wrote in message
news:1169741996.299835.197860@.l53g2000cwa.googlegr oups.com...
>I am looking to shred it into relational form. Planning to populate
> several tables based on the data contained in the file. Once that is
> done the xml file can be either deleted or archived. Thanks,
> Adi
> On Jan 24, 4:51 pm, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
> wrote:
>