-
XML help needed
I have a table METRIC_MESSAGE, that has a column XML_MESSAGE that has a data type of LONG containing data in an XML format. I need to write a query that will take the data in XML_MESSAGE and be able to insert the elements into columns in another table.
Here is a simple example:
This would be the data contained in the XML_MESSAGE:
(NOTE: Please ignore the "<" it should only be a < but I could not get that to appear)
Code:
"<"message notification="jacob.mccarthy@eds.com" time_zone= "GMT" message_sent= "5/31/2005 12:00:04 AM" >
"<"metric code="AVAILABILITY" >
"<"record server="WHITE" instance="EDHP1" downtime_start="2005-05-20 00:00:00" downtime_end="2005-05-21 00:00:04" downtime="50" />
"<"record server="WHITE" instance="EDHP1" downtime_start="2005-05-12 00:00:00" downtime_end="2005-05-13 00:00:04" downtime="75" />
"<"/metric>
"<"/message>
My table would be:
Code:
CREATE TABLE VALIDATE_METRIC
(server VARCHAR2(128),
instance VARCHAR2(128),
downtime_start DATE,
downtime_end DATE,
message_sent DATE,
notification VARCHAR2(128),
time_zone VARCHAR2(10),
downtime NUMBER)
ON COMMIT PRESERVE ROWS
/
I need to insert the values for: notification, time_zone, and message_sent from 's into the their appropriate columns in the VALIDATE_METRIC table.
In SQL server 2000 I was easily able to do this with:
sp_xml_preparedocument, and sp_xml_removedocument
The code to insert the xml data into my table variable was
Code:
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
INSERT INTO @table_xml
SELECT code, server, NULL, instance, NULL, downtime_start, downtime_end, message_sent, notification,
time_zone, downtime
FROM OPENXML
(@hdoc, '/message/metric/record',1)
WITH (code varchar(30) '../@code',
server varchar(128),
instance varchar(128),
downtime_start varchar(30),
downtime_end varchar(30),
message_sent varchar(30) '../../@message_sent',
notification varchar(128) '../../@notification',
time_zone varchar(10) '../../@time_zone',
downtime varchar(10)
)
EXEC sp_xml_removedocument @hdoc
Can anyone tell me if there is a similar query that I can write in Oracle to do this?
Thanks...
Last edited by patoink; 07-11-2005 at 06:29 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|