answered question
0
Votes
Votes
1
Answer
Answer
M$3.00
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...?!
|
Report
answers (1)
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
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
Related questions
140 characters left
ask any question
