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.
Showing posts with label lineitems. Show all posts
Showing posts with label lineitems. Show all posts
Monday, March 19, 2012
Subscribe to:
Posts (Atom)