from XML file to sqlserver table

Hello; I'm a newby with openxml; I'm sorry in advance for my question. I've
found a lot of documentation about openxml but I've still a doubt...
In this simple example I'm able to load in a table an extract of a xml file:
here you are:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a INSERT statement that uses the OPENXML rowset provider.
INSERT INTO CUSTOMER
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
The question is very simple, probably; how can I manage the file without
"embed it" in to the code between the ' ' (set @.doc='...')?
In fact my file is potentially large and I would like to open the file and
insert the record dinamically without paste it in the procedure code....
Thanks in advance.
Bruno StefanuttiThe best way is to put the expression into a stored procedure and pass the
value as a paramater (you can use TEXT or NTEXT as the parameter type).
Best regards
Michael
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
> Hello; I'm a newby with openxml; I'm sorry in advance for my question.
> I've
> found a lot of documentation about openxml but I've still a doubt...
> In this simple example I'm able to load in a table an extract of a xml
> file:
> here you are:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order CustomerID="VINET" EmployeeID="5"
> OrderDate="1996-07-04T00:00:00">
> <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
> <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order CustomerID="LILAS" EmployeeID="3"
> OrderDate="1996-08-16T00:00:00">
> <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> --Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a INSERT statement that uses the OPENXML rowset provider.
> INSERT INTO CUSTOMER
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> The question is very simple, probably; how can I manage the file without
> "embed it" in to the code between the ' ' (set @.doc='...')?
> In fact my file is potentially large and I would like to open the file
> and
> insert the record dinamically without paste it in the procedure code....
> Thanks in advance.
> Bruno Stefanutti
>
>|||...When you say "expression" this means I have to pass the path of my xml
file as a parameter? Or you intend every "slice" of the file? Can you explai
n
a little example?
When I receive the file xml to import, I don't know his dimension.
Thanks in advance.
Bruno Stefanutti
"Michael Rys [MSFT]" wrote:

> The best way is to put the expression into a stored procedure and pass the
> value as a paramater (you can use TEXT or NTEXT as the parameter type).
> Best regards
> Michael
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C8FAB527-D353-4858-9D3B-0E2B6D2DD2D1@.microsoft.com...
>
>|||No I mean the actual content of the XML.
Here is an example:
create procedure example @.x NTEXT
as
declare @.h int
exec sp_xml_preparedocument @.h OUTPUT, @.x
SELECT *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName')
SELECT *
FROM OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
CustomerID varchar(5) '../@.ID',
OrderID varchar(5),
OrderDate datetime)
select *
FROM OpenXML(@.h,'//Customer')
WITH (ID int '@.mp:id',
CustomerID varchar(5) '@.ID',
CompanyName varchar(32) '@.name',
ContactName varchar(32) '@.ContactName') C
JOIN OpenXML(@.h,'//Order')
WITH (ParentID int '@.mp:parentid',
OrderID varchar(5),
OrderDate datetime) O
ON C.ID=O.ParentID
exec sp_xml_removedocument @.h
go
-- The following is a TSQL call to the stored proc. Otherwise you use your
favorite client provider to call the procedure with the parameter
exec example N'<root>
<Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="8000"
Country="Switzerland" Phone="555-555555" Fax="555-555555">
<Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="29.46" />
<Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
ShipVia="1" Freight="149.46" />
</Customer>
</root>'
"consept" <consept@.discussions.microsoft.com> wrote in message
news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...
> ...When you say "expression" this means I have to pass the path of my xml
> file as a parameter? Or you intend every "slice" of the file? Can you
> explain
> a little example?
> When I receive the file xml to import, I don't know his dimension.
> Thanks in advance.
> Bruno Stefanutti
> "Michael Rys [MSFT]" wrote:
>|||I tried using the text type before. It doesnt' allow it. How is it working
for you?
"Michael Rys [MSFT]" wrote:

> No I mean the actual content of the XML.
> Here is an example:
> create procedure example @.x NTEXT
> as
> declare @.h int
> exec sp_xml_preparedocument @.h OUTPUT, @.x
> SELECT *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName')
> SELECT *
> FROM OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> CustomerID varchar(5) '../@.ID',
> OrderID varchar(5),
> OrderDate datetime)
> select *
> FROM OpenXML(@.h,'//Customer')
> WITH (ID int '@.mp:id',
> CustomerID varchar(5) '@.ID',
> CompanyName varchar(32) '@.name',
> ContactName varchar(32) '@.ContactName') C
> JOIN OpenXML(@.h,'//Order')
> WITH (ParentID int '@.mp:parentid',
> OrderID varchar(5),
> OrderDate datetime) O
> ON C.ID=O.ParentID
> exec sp_xml_removedocument @.h
> go
> -- The following is a TSQL call to the stored proc. Otherwise you use your
> favorite client provider to call the procedure with the parameter
> exec example N'<root>
> <Customer ID="DEMO1" name="Demo.com" ContactName="Michael Rys"
> ContactTitle="CEO" Address="Nebenstrasse 15" City="Zurich" PostalCode="800
0"
> Country="Switzerland" Phone="555-555555" Fax="555-555555">
> <Order OrderID="42" EmployeeID="6" OrderDate="2000-08-25T00:00:00"
> RequiredDate="2000-09-22T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="29.46" />
> <Order OrderID="43" EmployeeID="6" OrderDate="2000-09-25T00:00:00"
> RequiredDate="2000-09-30T00:00:00" ShippedDate="2000-09-02T00:00:00"
> ShipVia="1" Freight="149.46" />
> </Customer>
> </root>'
>
> "consept" <consept@.discussions.microsoft.com> wrote in message
> news:C6CEBD2B-477B-42B7-93D7-3FF899CECE9E@.microsoft.com...
>
>|||Did you try the example below? Note that the text type can only be used as a
parameter and not a variable in SQL Server 2000 (this will work with SQL
Server 2005).
Best regards
Michael
"Reese77" <Reese77@.discussions.microsoft.com> wrote in message
news:3D38697A-2E7A-4BC2-AABD-6CC8017E32A3@.microsoft.com...
>I tried using the text type before. It doesnt' allow it. How is it
>working
> for you?
> "Michael Rys [MSFT]" wrote:
>