Home » SQL & PL/SQL » SQL & PL/SQL » XML elements to table columns
XML elements to table columns [message #187857] Wed, 16 August 2006 00:57 Go to next message
dw_mmr
Messages: 12
Registered: July 2006
Junior Member
Hi ,

I have an xml file which is not the stanard tag separated format.
I have elements which need to be mapped to my oracle table

================================================================
eg:
<?xml version="1.0"?>
<!DOCTYPE ICN_MSG03_Notify_Address SYSTEM "ICN_MSG03_Notify_Address.dtd">
<ICN_MSG03_Notify_Address>
<msg_header msgid="908989089080" source="CN" destination="IRIS" msgdate="20060811" msgtime="124443" seq="1" msgtriggertype="EDIT">
</msg_header>
<msg_content party_id="1000" epr_guid="19876" iris_contact_guid="100" address_guid="876768" address_line1="29 A Street" address_line2="A Town" address_line3="Somewhere" address_line4="" address_district="" address_electoral_ward="" address_postcode="ZZN 123" address_email="someone@somewhere.com" address_start_date="20060201" address_end_date="">
<phone_number phone_number="(1234) 12345789" phone_type="WORK">
</phone_number>
<phone_number phone_number="HOME" phone_type="(5432) 98798798">
</phone_number>
</msg_content>
</ICN_MSG03_Notify_Address>
=================================================================

here party_id , epr_guid ,iris_contact_guid ec (elements within msg_content tab) are corresponding to my columns in oracle table


can someone provide me with any sample code available which deals with an xml file of such a format??


thanks
Re: XML elements to table columns [message #187894 is a reply to message #187857] Wed, 16 August 2006 04:52 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Extract/ExtractValue functions can get attribute values from XML of this format.

Examples:
Link 1
Link 2

HTH
Re: XML elements to table columns [message #188115 is a reply to message #187857] Thu, 17 August 2006 03:10 Go to previous messageGo to next message
dw_mmr
Messages: 12
Registered: July 2006
Junior Member
hi ,
I am a little confused because im working on xml for the first time..

from the links provided above , the main objective is to extract
based on some conditions wihtin the attributes
( eg : ""Can anyone please help me extract the following record
For <ItemOut quantity="1" lineNumber="1">
I need Id field of <Segment> tag if type field of <Segment> tag is "Action"?????"" wihin the link )

What i am trying to obtain is map my attributes
(party_id , epr_guid , iris_contact_guid etc to my oracle table )

my table is create table MM_ADDRESS
(
PARTY_ID NUMBER(10),
EPR_GUID NUMBER(10),
IRIS_CONTACT_GUID NUMBER(5),
ADDRESS_GUID VARCHAR2(50),
ADDRESS_LINE1 VARCHAR2(100),
ADDRESS_LINE2 VARCHAR2(100),
ADDRESS_LINE3 VARCHAR2(100),
ADDRESS_LINE4 VARCHAR2(100),
ADDRESS_DISTRICT VARCHAR2(50),
ADDRESS_POSTCODE VARCHAR2(50),
ADDRESS_EMAIL VARCHAR2(50),
ADDRESS_START_DATE DATE,
ADDRESS_END_DATE DATE,
PHONE_NUMBER VARCHAR2(30),
PHONE_TYPE VARCHAR2(20)
)

I would like my program to pick up value 1000 for party_id "19876" for epr_guid etc ( based on xml file)
Re: XML elements to table columns [message #188129 is a reply to message #187857] Thu, 17 August 2006 03:59 Go to previous messageGo to next message
dw_mmr
Messages: 12
Registered: July 2006
Junior Member
i also have the dtd file avaliable ..
will this in anyway hlp me to load data
( party_id , epr_guid tc columns wihtin element msg_content) from xml to oracle?


<!ELEMENT ICN_MSG03_Notify_Address (msg_header,
msg_content)>

<!ELEMENT msg_header (#PCDATA)>

<!ATTLIST msg_header
msgtype CDATA #FIXED "ICN_MSG03_Notify_Address"
msgid CDATA #REQUIRED
source CDATA #REQUIRED
destination CDATA #REQUIRED
msgdate CDATA #REQUIRED
msgtime CDATA #REQUIRED
seq CDATA #REQUIRED
msgtriggertype CDATA #REQUIRED
>
<!ELEMENT msg_content (phone_number*)>

<!ATTLIST msg_content
party_id CDATA #REQUIRED
epr_guid CDATA #REQUIRED
iris_contact_guid CDATA #REQUIRED
address_guid CDATA #REQUIRED
address_line1 CDATA #REQUIRED
address_line2 CDATA #REQUIRED
address_line3 CDATA #REQUIRED
address_line4 CDATA #REQUIRED
address_district CDATA #REQUIRED
address_electoral_ward CDATA #REQUIRED
address_postcode CDATA #REQUIRED
address_email CDATA #REQUIRED
address_start_date CDATA #REQUIRED
address_end_date CDATA #REQUIRED
>
<!ELEMENT phone_number (#PCDATA)>

<!ATTLIST phone_number
phone_number CDATA #REQUIRED
phone_type (WORK|HOME|MOBILE) #REQUIRED
>
Re: XML elements to table columns [message #188142 is a reply to message #187857] Thu, 17 August 2006 04:40 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
Given the structure of your XML, the mapping between XML elements and the table columns will not be directly achieved. A couple of approaches that I can suggest:

1. Using EXTRACT/EXTRACTVALUE: Write PL/SQL to extract the attribute values (as shown in the quoted example) and in a loop, process each row with INSERT INTO... the table for the extracted values.

2. Using DBMS_XMLSAVE: This takes an XML document in canonical format and loads it into the table. The condition is that the XML must be in canonical format, like this:
<ROWSET>
  <ROW>
    <COL1>111</COL1>
    <COL2>Value1</COL2>
  </ROW>
  <ROW>
    <COL1>112</COL1>
    <COL2>Value2</COL2>
  </ROW>
</ROWSET>

There are parsing mechanisms available that can transform your XML into one in canonical form, after which you can call DBMS_XMLSAVE. These links should give you some ideas:
Link 1
Link 2
Link 3

Re: XML elements to table columns [message #188541 is a reply to message #187857] Sat, 19 August 2006 03:36 Go to previous messageGo to next message
dw_mmr
Messages: 12
Registered: July 2006
Junior Member
HI Hobbes ,

The functions are working fine an I am able to extract attribut
values , thanks.

however if the xml file has the DOCTYPE line within it

(<!DOCTYPE ICN_MSG03_Notify_Address SYSTEM "ICN_MSG03_Notify_Address.dtd">)

the procedure throws an error ERROR ORA-21700: object does not exist or is marked for delete.

Why doee this occur?
Is there anyway to handle this in Oracle itself ( other than rmemoving it from the input file using some unix/ text editing mechanisms)
Re: XML elements to table columns [message #188619 is a reply to message #187857] Mon, 21 August 2006 00:57 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
I'm afraid I have not worked with DTDs in Oracle XDB - I guess this error occurs because the referenced DTD is not registered with Oracle.

There are APIs to register XML schema - if you can do that DTDs as well, perhaps that would resolve your error. Else, you can take in the input XML as CLOB, remove the DOCTYPE tag using Oracle string manipulation functions and then convert the CLOB to XMLType.

See if the chapter Structured Mapping of XML Type from XML Database Developer's Guide is of help. Good luck!

Re: XML elements to table columns [message #188627 is a reply to message #188619] Mon, 21 August 2006 01:23 Go to previous message
dw_mmr
Messages: 12
Registered: July 2006
Junior Member
Yes , I guess string manipulation is the easy way out Smile.

I'll try for the APIs too when I get time.

Thanks for your help
Previous Topic: how to use sql query in VB6
Next Topic: insert into
Goto Forum:
  


Current Time: Sun Dec 04 18:31:14 CST 2016

Total time taken to generate the page: 0.10261 seconds