DBAsupport.com Forums - Powered by vBulletin
Results 1 to 1 of 1

Thread: XML help needed

  1. #1
    Join Date
    Jul 2005

    Exclamation 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)

    "<"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" />

    My table would be:
    	   (server VARCHAR2(128), 
    	    instance VARCHAR2(128), 
    	    downtime_start DATE, 
    	    downtime_end DATE, 
    	    message_sent DATE, 
    	    notification VARCHAR2(128), 
    	    time_zone VARCHAR2(10), 
    	    downtime NUMBER)
    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
    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?

    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

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.