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

Thread: XML help needed

  1. #1
    Join Date
    Jul 2005
    Posts
    2

    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)

    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 07: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