Next Question
RSS
I looked up OpenXML and I think this example in that doc is really similar to what you are doing with NewElementChildNode being like OrderDetail. I think you actually just have one fewer levels.
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<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',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
This is the result:
OrderID CustomerID OrderDate ProdID Qty
-------------------------------------------------------------
10248 VINET 1996-07-04 00:00:00.000 11 12
10248 VINET 1996-07-04 00:00:00.000 42 10
10283 LILAS 1996-08-16 00:00:00.000 72 3
Source(s):
http://msdn.microsoft.com/en-us/library/aa226522(SQL.80).aspx
Permalink | Report
Answered Question
M$3
December 31, 2008 11:11 PM
Convoluted Microsoft SQL and XML question...?!
I have a stored procedure which passes a reference to an XML document (SQL 2000 style) using an int variable @idoc to another proc.
The receiving proc extracts the elements to grab the appropriate field values. The document looks like this (note that less than and greater than signs are replaced by [ and ], respectively, due to Mahalo editor's formatting:
[Account]
[AccountInfo AccountDestination="Alpha AccountSource="myDatabase" AccountStatus="Active" /]
[/Account]
Currently, the proc uses this select to grab the data.
SELECT @AccountDestination = AccountDestination,
@AccountSource = AccountSource,
@AccountStatus = AccountStatus
FROM OPENXML(@idoc, '/Account/AccountInfo', 1) WITH (
@AccountDestination varchar(30),
@AccountSource varchar(50),
@AccountStatus varchar(30)
)
The XML document will be changing though such that a new element (NewElement) is added along with some child nodes (NewElementChildNode).
[Account]
[AccountInfo AccountDestination="Alpha AccountSource="myDatabase"
AccountStatus="Active" /]
[NewElement]
[NewElementChildNode Name="testName1" Address="testAddress1" /]
[NewElementChildNode Name="testName2" Address="testAddress2" /]
[/NewElement]
[/Account]
Now, there will be the NewElement, under which I need to be able to grab the data from each NewElementChildNode.
I'm stuck with SQL 2000 conventions here so I'm not able to use the new XML data type provided in 2005, for example.
How can I do this using a similar SELECT with the OPENXML syntax? Do I need to cursor through however many NewElementChildNode elements and process each child node individually, or...?!
The receiving proc extracts the elements to grab the appropriate field values. The document looks like this (note that less than and greater than signs are replaced by [ and ], respectively, due to Mahalo editor's formatting:
[Account]
[AccountInfo AccountDestination="Alpha AccountSource="myDatabase" AccountStatus="Active" /]
[/Account]
Currently, the proc uses this select to grab the data.
SELECT @AccountDestination = AccountDestination,
@AccountSource = AccountSource,
@AccountStatus = AccountStatus
FROM OPENXML(@idoc, '/Account/AccountInfo', 1) WITH (
@AccountDestination varchar(30),
@AccountSource varchar(50),
@AccountStatus varchar(30)
)
The XML document will be changing though such that a new element (NewElement) is added along with some child nodes (NewElementChildNode).
[Account]
[AccountInfo AccountDestination="Alpha AccountSource="myDatabase"
AccountStatus="Active" /]
[NewElement]
[NewElementChildNode Name="testName1" Address="testAddress1" /]
[NewElementChildNode Name="testName2" Address="testAddress2" /]
[/NewElement]
[/Account]
Now, there will be the NewElement, under which I need to be able to grab the data from each NewElementChildNode.
I'm stuck with SQL 2000 conventions here so I'm not able to use the new XML data type provided in 2005, for example.
How can I do this using a similar SELECT with the OPENXML syntax? Do I need to cursor through however many NewElementChildNode elements and process each child node individually, or...?!
Interesting Question?
Yes (0)
No (0)
- In Web Development |
- |
- Report |
-
Share
RSS
Best Answer Chosen by Asker
| January 01, 2009 01:10 AM |
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<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',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity')
This is the result:
OrderID CustomerID OrderDate ProdID Qty
-------------------------------------------------------------
10248 VINET 1996-07-04 00:00:00.000 11 12
10248 VINET 1996-07-04 00:00:00.000 42 10
10283 LILAS 1996-08-16 00:00:00.000 72 3
Source(s):
http://msdn.microsoft.com/en-us/library/aa226522(SQL.80).aspx
Permalink | Report
Answer this Question
Related Questions
Ask a Question
Buy Mahalo Dollars with Credit Card or PayPal
Top Members
Most Popular Tags
Categories
- Anonymous
- Arts & Design
- Beauty & Style
- Books & Authors
- Business
- Cars & Transportation
- Consumer Electronics
- Coupons Deals
- Education
- Entertainment
- Environment
- Fitness
- Food & Drink
- From Email
- From Iphone
- From Twitter
- Health
- History
- Hobbies
- Home & Garden
- How Tos
- Humor
- Jobs
- Legal
- Local
- Love & Relationships
- Mahalo Answers Community
- Money
- Music
- News
- NSFW
- Parenting
- Pets
- Science & Mathematics
- Services
- Shopping
- Social Science
- Society & Culture
- Sports
- Technology & Internet
- Travel
- Video Games
Welcome New Members
- willfawcett, November 15, 2009 05:49 PM
- snookerloopy, November 15, 2009 05:45 PM
- bingdr_76, November 15, 2009 05:32 PM
- preplannedmyway, November 15, 2009 05:29 PM
- ilinochka, November 15, 2009 05:18 PM
Mahalo Dollars are the currency of Mahalo Answers.
Each Mahalo Dollar costs $1.
Once you earn more than 40 Mahalo Dollars, you can request to be paid via PayPal. Each Mahalo Dollar is currently worth $0.75 when paid out via PayPal. Learn More