Showing posts with label openxml. Show all posts
Showing posts with label openxml. Show all posts

Monday, March 19, 2012

How to use OPENXML to retrieve detail lineitems

Hi there,
I was trying to retrieve all LineItems from the following query. However,
if I execute the following query, it only returns the First item. How could
I modify the OPENXML synatax so it will also return the Second item and Thir
d
item in one select statement like this:
VINET 1996-07-04 00:00:00.000 11 12 First item
VINET 1996-07-04 00:00:00.000 11 12 Second item
VINET 1996-07-04 00:00:00.000 11 12 Third item
VINET 1996-07-04 00:00:00.000 42 10 NULL
LILAS 1996-08-16 00:00:00.000 72 3 NULL
What I would like to do is to insert all fields on the XML doc including
these LineItem into a database table.
Thanks.
Abel Chan
-- ******************
-- ****** Query ******
-- ******************
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12">
<LineItem>First item</LineItem>
<LineItem>Second item</LineItem>
<LineItem>Third item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@.ProductID',
Qty int '@.Quantity',
LineItem VARCHAR(50) './LineItem' )
EXEC sp_xml_removedocument @.idocAbel
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12">
<LineItem>First item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="10">
<LineItem>Second item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="12">
<LineItem>Third item</LineItem>
</OrderDetail>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- <LineItem>Second item</LineItem>
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@.ProductID',
Qty int '@.Quantity',
LineItem VARCHAR(50) './LineItem' )
EXEC sp_xml_removedocument @.idoc
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:70D61777-6563-4323-B97C-310113BD2321@.microsoft.com...
> Hi there,
> I was trying to retrieve all LineItems from the following query. However,
> if I execute the following query, it only returns the First item. How
> could
> I modify the OPENXML synatax so it will also return the Second item and
> Third
> item in one select statement like this:
> VINET 1996-07-04 00:00:00.000 11 12 First item
> VINET 1996-07-04 00:00:00.000 11 12 Second item
> VINET 1996-07-04 00:00:00.000 11 12 Third item
> VINET 1996-07-04 00:00:00.000 42 10 NULL
> LILAS 1996-08-16 00:00:00.000 72 3 NULL
>
> What I would like to do is to insert all fields on the XML doc including
> these LineItem into a database table.
> Thanks.
> Abel Chan
> -- ******************
> -- ****** Query ******
> -- ******************
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order EmployeeID="5" >
> <OrderID>10248</OrderID>
> <CustomerID>VINET</CustomerID>
> <OrderDate>1996-07-04T00:00:00</OrderDate>
> <OrderDetail ProductID="11" Quantity="12">
> <LineItem>First item</LineItem>
> <LineItem>Second item</LineItem>
> <LineItem>Third item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order EmployeeID="3" >
> <OrderID>10283</OrderID>
> <CustomerID>LILAS</CustomerID>
> <OrderDate>1996-08-16T00:00:00</OrderDate>
> <OrderDetail ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT stmt using OPENXML rowset provider.
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
> WITH (CustomerID varchar(10) '../CustomerID',
> OrderDate datetime '../OrderDate',
> ProdID int '@.ProductID',
> Qty int '@.Quantity',
> LineItem VARCHAR(50) './LineItem' )
> EXEC sp_xml_removedocument @.idoc
>
>|||Hi Uri,
Thanks to your prompt reply. However, the xml document I got has multiple
<LineItem> under each <OrderDetail>. I wish each <OrderDetail> has only one
<LineItem>.
Is there a way to extract all the <LineItem> out? Even it is in a plain xml
text. With that, I can use another OPENXML to parse it.
Thanks Uri.
Abel
"Uri Dimant" wrote:

> Abel
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order EmployeeID="5" >
> <OrderID>10248</OrderID>
> <CustomerID>VINET</CustomerID>
> <OrderDate>1996-07-04T00:00:00</OrderDate>
> <OrderDetail ProductID="11" Quantity="12">
> <LineItem>First item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="10">
> <LineItem>Second item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="12">
> <LineItem>Third item</LineItem>
> </OrderDetail>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order EmployeeID="3" >
> <OrderID>10283</OrderID>
> <CustomerID>LILAS</CustomerID>
> <OrderDate>1996-08-16T00:00:00</OrderDate>
> <OrderDetail ProductID="72" Quantity="3"/>
>
> </Order>
> </Customer>
> </ROOT>'
> -- <LineItem>Second item</LineItem>
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT stmt using OPENXML rowset provider.
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
> WITH (CustomerID varchar(10) '../CustomerID',
> OrderDate datetime '../OrderDate',
> ProdID int '@.ProductID',
> Qty int '@.Quantity',
> LineItem VARCHAR(50) './LineItem' )
> EXEC sp_xml_removedocument @.idoc
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:70D61777-6563-4323-B97C-310113BD2321@.microsoft.com...
>
>|||Hello,
I test the following code and it works fine on my side:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12">
<LineItem>First item</LineItem>
<LineItem>Second item</LineItem>
<LineItem>Third item</LineItem>
</OrderDetail>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail/LineItem')
WITH (
CustomerID varchar(10) '//CustomerID',
OrderDate datetime '//OrderDate',
ProdID int '//@.ProductID',
Qty int '//@.Quantity',
LineItem VARCHAR(50) 'text()')
union
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@.ProductID',
Qty int '@.Quantity',
LineItem VARCHAR(50) './LineItem' )
order by customerID desc
EXEC sp_xml_removedocument @.idoc
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||That is exactly want I need. Union does the trick. :> Thanks so much Soph
ie.
BTW, I came up with an alternative while I was waiting for your reply.
I setup a #temp table by inserting all the LineItems. I also insert
customerId and Orderdate so I can find the LineItem with these two
identifiers. Then I use cursor to loop through the #temp. It works but
compare to yours, your solution is way cleaner and way easier to handle any
exception errors I will replace mine with your code.
Thanks again.
Abel Chan
"Sophie Guo [MSFT]" wrote:

> Hello,
> I test the following code and it works fine on my side:
>
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> <Order EmployeeID="5" >
> <OrderID>10248</OrderID>
> <CustomerID>VINET</CustomerID>
> <OrderDate>1996-07-04T00:00:00</OrderDate>
> <OrderDetail ProductID="11" Quantity="12">
> <LineItem>First item</LineItem>
> <LineItem>Second item</LineItem>
> <LineItem>Third item</LineItem>
> </OrderDetail>
> <OrderDetail ProductID="42" Quantity="10"/>
> </Order>
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> <Order EmployeeID="3" >
> <OrderID>10283</OrderID>
> <CustomerID>LILAS</CustomerID>
> <OrderDate>1996-08-16T00:00:00</OrderDate>
> <OrderDetail ProductID="72" Quantity="3"/>
> </Order>
> </Customer>
> </ROOT>'
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT stmt using OPENXML rowset provider.
>
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail/LineItem')
> WITH (
> CustomerID varchar(10) '//CustomerID',
> OrderDate datetime '//OrderDate',
> ProdID int '//@.ProductID',
> Qty int '//@.Quantity',
> LineItem VARCHAR(50) 'text()')
> union
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer/Order/OrderDetail')
> WITH (CustomerID varchar(10) '../CustomerID',
> OrderDate datetime '../OrderDate',
> ProdID int '@.ProductID',
> Qty int '@.Quantity',
> LineItem VARCHAR(50) './LineItem' )
> order by customerID desc
>
> EXEC sp_xml_removedocument @.idoc
>
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ========================================
=============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||Hello,
I am glad to hear that the informaion is helpful. Have a nice day!
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Wednesday, March 7, 2012

How to use content of file for xml input?

Hi Everyone,
actually i'm starting to work with OpenXML on SQL Server 2k. At the
examples i found everywhere a variable was defined and filled
hardcode. Something like this:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc = 'This is the content of my file'
EXEC sp_xml_prepraredocument @.idoc OUTPUT, @.doc
But i don't have any hardcoded text. I have a lot of xml files, which
liked to be prepared. They size vary from 1 MB to 100 MB and i don't
know how to pass them into the stored procedure.
So can anyone tell me, how a external xml file can be prepared to use
it with sp_xml_preparedocument' What functions are needed to read the
content of a given file and access it for a stored procedure'
Best regards,
OliverYou write a stored procedure with an ntext typed parameter and you read the
XML on the client and pass it to the stored procedure as a parameter. You
will have to change encoding to UTF-16 on the midtier before you pass it in
though.
Best regards
Michael
"Oliver" <oliver.muenchow@.gmx.de> wrote in message
news:1167740889.192836.302290@.48g2000cwx.googlegroups.com...
> Hi Everyone,
> actually i'm starting to work with OpenXML on SQL Server 2k. At the
> examples i found everywhere a variable was defined and filled
> hardcode. Something like this:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc = 'This is the content of my file'
> EXEC sp_xml_prepraredocument @.idoc OUTPUT, @.doc
> But i don't have any hardcoded text. I have a lot of xml files, which
> liked to be prepared. They size vary from 1 MB to 100 MB and i don't
> know how to pass them into the stored procedure.
> So can anyone tell me, how a external xml file can be prepared to use
> it with sp_xml_preparedocument' What functions are needed to read the
> content of a given file and access it for a stored procedure'
> Best regards,
> Oliver
>|||Hi Michael,
ok, but doesn't there any method exists to do it directly on the SQL
server' I don't need a real client. I just have to read a XML file
into a table. Couldn't it be done directly on the server?
Best regards,
Oliver
Michael Rys [MSFT] schrieb:
> You write a stored procedure with an ntext typed parameter and you read th
e
> XML on the client and pass it to the stored procedure as a parameter. You
> will have to change encoding to UTF-16 on the midtier before you pass it i
n
> though.
> Best regards
> Michael
> "Oliver" <oliver.muenchow@.gmx.de> wrote in message
> news:1167740889.192836.302290@.48g2000cwx.googlegroups.com...|||You could write your own extended stored procedure or use the sp_OA stored
procs. Neither I recommend. SQL Server 2005 can do it easily with
OpenRowset(BULK).
Best regards
Michael
"Oliver" <oliver.muenchow@.gmx.de> wrote in message
news:1167808359.527451.19810@.a3g2000cwd.googlegroups.com...
> Hi Michael,
> ok, but doesn't there any method exists to do it directly on the SQL
> server' I don't need a real client. I just have to read a XML file
> into a table. Couldn't it be done directly on the server?
> Best regards,
> Oliver
> Michael Rys [MSFT] schrieb:
>
>|||Hi Michael,
thanks for the answer but i found a solution.
I used the bulk load mechanism. If you know what you need you know what
you have to search for. ;-) So after i read something about bulk load
and a little search i found the following website:
http://msdn2.microsoft.com/en-us/library/aa225754.aspx
With these mechasim i'm able to read the file into our SQL Server.
So thanks for your interest in my problem and best regards,
Oliver
Michael Rys [MSFT] schrieb:
> You could write your own extended stored procedure or use the sp_OA stored
> procs. Neither I recommend. SQL Server 2005 can do it easily with
> OpenRowset(BULK).
> Best regards
> Michael
> "Oliver" <oliver.muenchow@.gmx.de> wrote in message
> news:1167808359.527451.19810@.a3g2000cwd.googlegroups.com...

How to use content of file for xml input?

Hi Everyone,
actually i'm starting to work with OpenXML on SQL Server 2k. At the
examples i found everywhere a variable was defined and filled
hardcode. Something like this:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc = 'This is the content of my file'
EXEC sp_xml_prepraredocument @.idoc OUTPUT, @.doc
But i don't have any hardcoded text. I have a lot of xml files, which
liked to be prepared. They size vary from 1 MB to 100 MB and i don't
know how to pass them into the stored procedure.
So can anyone tell me, how a external xml file can be prepared to use
it with sp_xml_preparedocument? What functions are needed to read the
content of a given file and access it for a stored procedure?
Best regards,
Oliver
You write a stored procedure with an ntext typed parameter and you read the
XML on the client and pass it to the stored procedure as a parameter. You
will have to change encoding to UTF-16 on the midtier before you pass it in
though.
Best regards
Michael
"Oliver" <oliver.muenchow@.gmx.de> wrote in message
news:1167740889.192836.302290@.48g2000cwx.googlegro ups.com...
> Hi Everyone,
> actually i'm starting to work with OpenXML on SQL Server 2k. At the
> examples i found everywhere a variable was defined and filled
> hardcode. Something like this:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc = 'This is the content of my file'
> EXEC sp_xml_prepraredocument @.idoc OUTPUT, @.doc
> But i don't have any hardcoded text. I have a lot of xml files, which
> liked to be prepared. They size vary from 1 MB to 100 MB and i don't
> know how to pass them into the stored procedure.
> So can anyone tell me, how a external xml file can be prepared to use
> it with sp_xml_preparedocument? What functions are needed to read the
> content of a given file and access it for a stored procedure?
> Best regards,
> Oliver
>
|||Hi Michael,
ok, but doesn't there any method exists to do it directly on the SQL
server? I don't need a real client. I just have to read a XML file
into a table. Couldn't it be done directly on the server?
Best regards,
Oliver
Michael Rys [MSFT] schrieb:
[vbcol=seagreen]
> You write a stored procedure with an ntext typed parameter and you read the
> XML on the client and pass it to the stored procedure as a parameter. You
> will have to change encoding to UTF-16 on the midtier before you pass it in
> though.
> Best regards
> Michael
> "Oliver" <oliver.muenchow@.gmx.de> wrote in message
> news:1167740889.192836.302290@.48g2000cwx.googlegro ups.com...
|||You could write your own extended stored procedure or use the sp_OA stored
procs. Neither I recommend. SQL Server 2005 can do it easily with
OpenRowset(BULK).
Best regards
Michael
"Oliver" <oliver.muenchow@.gmx.de> wrote in message
news:1167808359.527451.19810@.a3g2000cwd.googlegrou ps.com...
> Hi Michael,
> ok, but doesn't there any method exists to do it directly on the SQL
> server? I don't need a real client. I just have to read a XML file
> into a table. Couldn't it be done directly on the server?
> Best regards,
> Oliver
> Michael Rys [MSFT] schrieb:
>
|||Hi Michael,
thanks for the answer but i found a solution.
I used the bulk load mechanism. If you know what you need you know what
you have to search for. ;-) So after i read something about bulk load
and a little search i found the following website:
http://msdn2.microsoft.com/en-us/library/aa225754.aspx
With these mechasim i'm able to read the file into our SQL Server.
So thanks for your interest in my problem and best regards,
Oliver
Michael Rys [MSFT] schrieb:
[vbcol=seagreen]
> You could write your own extended stored procedure or use the sp_OA stored
> procs. Neither I recommend. SQL Server 2005 can do it easily with
> OpenRowset(BULK).
> Best regards
> Michael
> "Oliver" <oliver.muenchow@.gmx.de> wrote in message
> news:1167808359.527451.19810@.a3g2000cwd.googlegrou ps.com...