validate value in xml file

Looks like "homework"??!!

no is not

So, what is it then?! Please explain!

Looks like specific organiation requirements.  Not sure how much we can help with those.

We can probably help with the parsing of the XML.  I would probably start looking at XMLTABLE to parse out the values:
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/XMLTABLE.html#GUID-C4A32C58-33E5-4CF1-A1FE-039550D3ECFA

Not sure how much help we can be with what you need to do with those parsed values once you get them.

i need to map those value from xml to database table, i need to add those validation in condition,i have xml file in datbase table already

declare
   textclob     clob;
   viigno varchar2(100);
   vcheck number;
   viig_desc varchar2(1000);
   viig_date varchar2(1000);
   v_mrc varchar2(20);
   v_text varchar2(20);
   v_defination varchar2(50);
   v_ReplyInstructions varchar2(30);
   v_example varchar2(50);
   v_code varchar2(5);
   v_INC_AIN varchar(300);
Begin
   if :xml_data_iig.rowid is null then
        message ('NoXML data !! '); message (' ');
        return ;
   end if;    
   
 
   
   SELECT
      myextract(IIG_XML,'/ItemName/@Code','1'),
      myextract(IIG_XML,'/ItemName/Name','2')
      into
      v_code,
      v_INC_AIN
      from noc_xml_data_iig where rowid=:xml_data_iig.rowid;
     
     
      message('vcode'||v_code);
      message('inc'||v_INC_AIN);


     --message ('000 !!');
     -- message (' ');        
        select count(*) into vcheck from noc_inc  where inc_code=v_code;
       
        message('count'|| vcheck);


        if nvl(vcheck,0)= 0 then
            insert into noc_inc (INC_CODE,
                                 INC_AIN,
                              --   INC_CONDITION_CODE          ,        
                                          -- INC_DELIMITATION            ,
                                           INC_CREATED_DATE   ,          
                                           INC_CREATED_BY,      
                                       --    INC_MODIFIED_DATE           ,          
                                          -- INC_MODIFIED_BY            
                             INC_STATUS)
         values ( v_code,
                  v_INC_AIN,
                  sysdate,
                  user,
                  'A');
        end if;
       
         forms_ddl ('commit');
     
     -- message ('completed !!');
     -- message (' ');
     
       exception
     when others
     then
       message('File to map xml file: '||sqlerrm);


End;


create or replace function myextract (vxml clob,vpath varchar2,voption varchar2) return varchar2 as
         vr varchar2(1000);
     begin
        if voption='1' then
           select extract (xmltype(vxml),vpath).getStringVal() into vr from dual;
        elsif voption='2' then
           select extractvalue(xmltype(vxml),vpath) into vr from dual;
        end if;
        return vr;
     end;

Select allOpen in new window

As XML validation is something different from that what your description indicates, please rephrase your question.

Without knowing anything about your XML, nor size neither structure and the involved other tables:

Parse your XML into temporary tables. Then apply the logic described in your image in SQL.

p.s. afaik the description seems not to be as precise as it should be. "Insert [..] on" is not really a term in SQL or XML or XSLT. Links are known in XSD...

hi this my xml sample,the validation must be done with the mapping
80773.txt

the xml is in this table
CREATE TABLE "NOC_XML_DATA_IIG"
   (   "IIG_NO" VARCHAR2(20 BYTE) NOT NULL ENABLE,
   "IIG_DATA" "XMLTYPE",
   "IIG_XML" CLOB NOT NULL ENABLE,
   "CREATED_BY" VARCHAR2(50 BYTE) DEFAULT ON NULL user NOT NULL ENABLE,
   "CREATED_DATE" TIMESTAMP (6) DEFAULT ON NULL sysdate
    --primary key(IIG_NO,IIG_XML)
   NOT NULL ENABLE
   )

Select allOpen in new window

Parse your XML into temporary tables. Then apply the logic described in your image in SQL.

If you have the XML already stored as XMLTYPE in a table (NOC_XML_DATA_IIG) as you mentioned above, where exactly is your problem?! Just "iterate through" this stored data and apply your checks, that's it ;-)

how do i do the check
a.      INC_CODE = Code in
<ItemName FIIG_4065=’$#####” Code =”#####”
b.      INC_AIN = <Name>$$$$$</Name>
c.      INC_CONDITION_CODE = 1 or 2
•      Under <FSCs>
If <Key>####</Key> is displayed only once then insert 1
If <Key>####</Key> is displayed more than once insert 2
d.      INC_DELIMITATION = <Description>$$$</Description>
e.      INC_CREATED_DATE = sysdate
f.      INC_CREATED_BY = user_name running the XML Load program.
g.      INC_STATUS = “A”

and those validation

Those are business rules that we cannot help with because we don't know your system.  You will need to work with the person that gave you those requirements to understand what they mean.

Those are business rules that we cannot help with because we don't know your sysem.

Indeed.

For example, your rule 1:
Neither NSC_CODE nor NOC_NAT_SUPPLY_CLASS exists in your XML.. what does this rule mean?

i must map data from xml to those tables
this table and others

CREATE TABLE "NOC_NAT_SUPPLY_CLASS"
   (   "NSC_CODE" VARCHAR2(4 BYTE) NOT NULL ENABLE,
   "NSC_DESC" VARCHAR2(150 BYTE) NOT NULL ENABLE,
   "NSC_CREATED_DATE" DATE NOT NULL ENABLE,
   "NSC_CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
   "NSC_MODIFIED_DATE" DATE,
   "NSC_MODIFIED_BY" VARCHAR2(30 BYTE),
    CONSTRAINT "NSC_PK" PRIMARY KEY ("NSC_CODE"))
    
    CREATE TABLE "NOC_INC"
   (   "INC_CODE" VARCHAR2(5 BYTE) NOT NULL ENABLE,
   "INC_AIN" VARCHAR2(300 BYTE) NOT NULL ENABLE,
   "INC_CONDITION_CODE" CHAR(1 BYTE),
   "INC_DELIMITATION" VARCHAR2(1500 BYTE),
   "INC_CREATED_DATE" DATE NOT NULL ENABLE,
   "INC_CREATED_BY" VARCHAR2(30 BYTE) NOT NULL ENABLE,
   "INC_MODIFIED_DATE" DATE,
   "INC_MODIFIED_BY" VARCHAR2(30 BYTE),
   "INC_STATUS" CHAR(1 BYTE),
    CONSTRAINT "INC_PK" PRIMARY KEY ("INC_CODE");

Select allOpen in new window

1. NSC_CODE = <Key>####</Key>
2. NSC_DESC = <Value>&&&&</Value>
3. NSC_CREATED_DATE = sysdate
4. NSC_CREATED_BY = user_name running the XML Load program.

 
 
 
 
 
 
 
 
 

hmm, you know that your requirements don't mention those mappings at all?

As I mentioned 9 hours ago, pulling the data out of the XML is pretty simple.  Looks like what I mentioned, XMLTABLE, would be my first choice.  The code and function you posted is horribly inefficient as it has to keep opening and parsing the XML data.

It's the "mapping" piece that we probably cannot help with because we don't know what that means.  The business rules we definitely cannot help with.  Those are the rules imposed by your company.  We don't know them.

Aside from a complete solution to ALL requirements, what are you looking for us to help you with in this question?

can you show me how can i do with XMLTABLE when i say to map  i mean to take data from xml and insert to database table

Do what?

Given the XML you uploaded, what do you want the results to look like?

Also, is the XML in the database stored in an XMLTYPE column, CLOB or something else?

map3.doc
80773.xml
i did send the xml its in clob

I saw the XML.

XMLTABLE is used in a select statement.  I want to know what you want the output of the select to look like when the XML is parsed.

the must be insert in this tables and other tables mention, i xml in both xmltype and clob we can use anytype

From what you posted in the original question, you need to insert into those tables based on specific business rules.

As we have said:  We cannot help you with that.  That will likely involve code.  Probably in a loop.

I can show you how to use XMLTABLE to extract the values in a cursor/result set.  What I need to know is the format of the results you want it in.

I CANNOT insert into the tables you provided because that involves the business rules...

So, using the result set similar to the TEMP table mentioned, what would you like the output from the parssed XML to look like?

[..] the must be insert in this tables [..]

hmm, your mapping document only specifies updates. No inserts.

Besides that, parse your XML. E.g. for the first table mentioned, it could be something like

SELECT  a.inc_code,
        a.inc_ain,
        a.inc_delimitation,
        b.key,
        b.value
FROM    XMLTable(
            '/ItemName'
            PASSING XmlType('
                <ItemName xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Code="80773" FIIG_4065="A605K0">
                  <Name>SNACK MIX</Name>
                  <Description>A commercially prepared food consisting of two or more items that are blended together for consumption.</Description>
                  <FSCs>
                    <KeyValuePair>
                      <Value>Special Dietary Foods and Food Specialty Preparations</Value>
                      <Key>8940</Key>
                    </KeyValuePair>
                    <KeyValuePair>
                      <Value>2Special Dietary Foods and Food Specialty Preparations</Value>
                      <Key>89402</Key>
                    </KeyValuePair>
                  </FSCs>
                </ItemName>
            ')
            COLUMNS
                inc_code VARCHAR2(5) PATH '@Code',
                inc_ain VARCHAR2(300) PATH 'Name',
                inc_delimitation VARCHAR2(1500) PATH 'Description',
                fscKeyValues XMLTYPE PATH 'FSCs/KeyValuePair'
        ) a,
        XMLTable(
            '/KeyValuePair'
            PASSING a.fscKeyValues
            COLUMNS
                key VARCHAR2(255) PATH 'Key',
                value VARCHAR2(255) PATH 'Value'
        ) b;

Select allOpen in new window

You can push this data into temporary tables, then parse the different tables from the data according to your description.

what do you mean temporary table

Select allOpen in new window

You shouldn't need a temp table.  Just pull the data directly from the select.

Did you run my sample? It parses the XML. What you do with the result data is yours.

But normally it is a good idea to parse all data first. This means that you should store it somewhere, a staging table, some variables, temporary tables, etc. before you proceed your data processing.

You shouldn't need a temp table.  Just pull the data directly from the select.

For tackling a problem or beginners it is better to store it, cause you can simpler debug your parsing and generate some reports from it.

how do i pull data directly select and insert can you show sample with one table or two

how do i pull data directly select and insert can you show sample with one table or two

Sorry to ask, but what is your Oracle / SQL level?

Taken the sample(s) from above, an INSERT may look like this:

insert into noc_inc(inc_code, inc_ain, inc_delimitation)
(SELECT  a.inc_code,
        a.inc_ain,
        a.inc_delimitation
FROM    XMLTable(
            '/ItemName'
            PASSING XmlType('
                <ItemName xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Code="80773" FIIG_4065="A605K0">
                  <Name>SNACK MIX</Name>
                  <Description>A commercially prepared food consisting of two or more items that are blended together for consumption.</Description>
                  <FSCs>
                    <KeyValuePair>
                      <Value>Special Dietary Foods and Food Specialty Preparations</Value>
                      <Key>8940</Key>
                    </KeyValuePair>
                    <KeyValuePair>
                      <Value>2Special Dietary Foods and Food Specialty Preparations</Value>
                      <Key>89402</Key>
                    </KeyValuePair>
                  </FSCs>
                </ItemName>
            ')
            COLUMNS
                inc_code VARCHAR2(5) PATH '@Code',
                inc_ain VARCHAR2(300) PATH 'Name',
                inc_delimitation VARCHAR2(1500) PATH 'Description',
                fscKeyValues XMLTYPE PATH 'FSCs/KeyValuePair'
        ) a,
        XMLTable(
            '/KeyValuePair'
            PASSING a.fscKeyValues
            COLUMNS
                key VARCHAR2(255) PATH 'Key',
                value VARCHAR2(255) PATH 'Value'
        ) b)

Select allOpen in new window

but this is hardcoded i what to pull from the noc_xml_data_iig table where the xml is

Go look at the examples of xmltable.

select
...
FROM    noc_xml_data_iig, XMLTable(
...
PASSING XmlType(YOUR_CLOB_COLUMN)
...

then add a where clause to pull in the CLOB you are after.