1 2 Previous Next 24 Replies Latest reply on Sep 5, 2014 1:44 PM by Marco Gralike Go to original post
      • 15. Re: Re: hi i am having issue in validating xml against xsd and inserting it into table....
        2737886

        Hi odie,

         

        I have gone through the exmple to refer here  and i am trying to say that i have register the schema(NewBusinessApplication.xsd).

        Now the problem which i am facing and what my requiements are like :-

         

        1. I have already been provided by a table which contain 7 columns in it among 7 clomuns there is a column(appl_xml) of xmltype.

            so i need to populate that table with some coditions.

             <A> I am suppose to validate input XML file (which i getting it as IN parameter in my PROC) agains XSD.

             <B> If that XML is valid than i need to read that XML to get some data from it before inserting it into my table into some local variables.

             <C> Then after fetching data from it i need to populate the TABLE along with that XML file and the data i fetch from the XML and some more

                    data which will be provided from outside.

             <D> Now if the XML is not valid then i'll put it in the ELSE block of the IF ELSE statement.

         

        2. So i have to perform this validation in a seperate function or procedure so that it can be use anywhere else for validation.

        3. Now the declaration block which i have sent you is validating the XML but not fetching data from it. As my requirement is to fetch some data from

           XML also so that i can use that fecthed data to populate my main table.(please check the SELECT  statement is the anonymous block)

        4.Am i writing the write sql statement for fetching data from a XML which contain NAMESPACES and other xml stuff in it.becuase this query works

          when i remove that namespaces and other stuff from the main NewBusinessApplication tag.

         

        So this is my basic problem with XML and i am still struggling to overcome with it.

        I hope now you understand what my requirement is and please help me if you DO !


        • 16. Re: Re: hi i am having issue in validating xml against xsd and inserting it into table....
          odie_63

          <A> I am suppose to validate input XML file (which i getting it as IN parameter in my PROC) agains XSD.

          OK, what problem do you have with that step?

          Use XMLType's schemaValidate() method, simple as that. You don't need a separate function.

           

               <B> If that XML is valid than i need to read that XML to get some data from it before inserting it into my table into some local variables.

               <C> Then after fetching data from it i need to populate the TABLE along with that XML file and the data i fetch from the XML and some more

                      data which will be provided from outside.

               <D> Now if the XML is not valid then i'll put it in the ELSE block of the IF ELSE statement.

          Again, what problem do you have?

          Do you require us to write the whole code down for you?

          Surely you can code an INSERT statement and an IF-THEN-ELSE block, can't you?

           

          4.Am i writing the write sql statement for fetching data from a XML which contain NAMESPACES and other xml stuff in it.becuase this query works

            when i remove that namespaces and other stuff from the main NewBusinessApplication tag.

          *Sigh*

          See again the last query in my previous example, it shows how to deal with namespaces and uses recommended function XMLTABLE to extract data.

          • 17. Re: Re: hi i am having issue in validating xml against xsd and inserting it into table....
            2737886

            in your code you have created a xmtype table and I am provided with a simple table which contain xmltype column in it.and at first that table is empty.so if the xml is valid then only I can insert it into that table but before inserting I need to fetch data from that xml

             

            in your last code you insert the xml first and fetch data from it. I can do that also but that is not my requirement . I am suppose to read the xml first (if it is valid)  like I do in my code using table function and do the further processing.

             

             

            and please don't feel troubled you not need to reply if I am not making any sense.

            BUT STILL I HAVE A LAST QUESTION

            do you know how can I use namespaces in exctractvalue().

            • 18. Re: Re: Re: hi i am having issue in validating xml against xsd and inserting it into table....
              odie_63

              and please don't feel troubled you not need to reply if I am not making any sense.

              BUT STILL I HAVE A LAST QUESTION

              do you know how can I use namespaces in exctractvalue().

              I'm not "troubled", just frustated that you don't seem to understand what I've been explaining so far.

              Why are you insisting on using extractvalue? DO NOT use extractvalue, it's deprecated, use XMLTABLE instead.

               

              If you feel compelled to use extractvalue from some reasons, just have the curiosity to look it up in the documentation, you'll find the syntax to use namespaces :

              http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions061.htm#SQLRF06173

               

               

              in your last code you insert the xml first and fetch data from it. I can do that also but that is not my requirement . I am suppose to read the xml first (if it is valid)  like I do in my code using table function and do the further processing.

              Look, I'm going to ask this one last time :

              What problem are you encountering? What prevents you from first reading some values before inserting, as you require. Just use the same XMLTABLE query I showed in my example, except that you won't query from the table, but directly from the XMLType variable :

               

              PASSING v_xml

              COLUMNS ...

              1 person found this helpful
              • 19. Re: hi i am having issue in validating xml against xsd and inserting it into table....
                2737886

                Hi Again Odie,

                 

                Now I am having a new requirement which is to generate the same XML as I have provided in the examples above.

                  and i'll be provided by 100's of IN parameter (some of these parameters are of TABLE type) for the procedure and using them I am suppose to generate the same XML.

                  So please can you guide me through the RIGHT approach to solve this.

                  I am asking you because I find you better here that at least you reply.

                 

                Thanks in advance

                • 20. Re: hi i am having issue in validating xml against xsd and inserting it into table....
                  Marco Gralike

                  Maybe the following is a good start

                   

                  generating dynamic xml structure and combining

                   

                  The Example XML is based on a official XML Schema

                   

                  Also read the bits and pieces around remarks like the following

                   

                   

                   

                  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


                  To make it a bit more "visible" for you I created a SQL file (down-loadable via DropBox) you can execute via SQL*Plus:

                   

                  https://dl.dropboxusercontent.com/u/26970143/OTN/TADIGEID-XML-SQL-Mapping-Template.zip

                   

                  It contains the SQL file and generated XML content.

                   

                  - SQL: https://dl.dropboxusercontent.com/u/26970143/OTN/TADIGEID-XML-SQL-Mapping-Template/TADIGEID-XML-SQL-Mapping-Template.sql

                  - XML: https://dl.dropboxusercontent.com/u/26970143/OTN/TADIGEID-XML-SQL-Mapping-Template/TADIGEID-XML-SQL-Mapping-Template.xml

                   

                  SQL*Plus command could be something like the following to generate new XML content

                   

                  $> sqlplus scott/tiger@orcl @TADIGEID-XML-SQL-Mapping-Template.sql


                  I didn't test for all SQL*Plus buffer issues, but fiddling with the ROWNUM values in the SQL statement would create smaller or bigger XML document. The last ROWNUM value can not be bigger than 1, otherwise you would create multiple ROOT elements and this is not allowed in a Wellformed XML document (just so you know when for example Firefox or Explorer throws you this error).


                  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

                  • 21. Re: Re: hi i am having issue in validating xml against xsd and inserting it into table....
                    odie_63

                    This is a separate issue.

                    Please open a new thread and make sure you provide again :

                    - the database version

                    - some sample input data

                    - the XML structure you have to generate from that data

                     

                    Thanks.

                    • 22. Re: hi i am having issue in validating xml against xsd and inserting it into table....
                      2737886

                      Hi Mark,

                      I am not able to the open link for sql query as I am in office they might have blocked that

                      • 23. Re: hi i am having issue in validating xml against xsd and inserting it into table....
                        2737886

                        Ok odie I'll be back with new thread with same question.

                        • 24. Re: hi i am having issue in validating xml against xsd and inserting it into table....
                          Marco Gralike

                          --

                          -- SQL Template to generate correct XML structure based on XML Schema: tadig-eid-10.0.xsd

                          --

                           

                          set trimspool on

                          set pages 0 long 100000000 lines 2000

                          set head off

                          set termout off

                          col XMLOUTPUT for a2000

                           

                          spool TADIGEID-XML-SQL-Mapping-Template.xml REPLACE

                           

                           

                          select XMLSERIALIZE (DOCUMENT (

                            xmlelement("TADIGEID"

                            , xmlattributes

                            ( 'https://infocentre.gsm.org/TADIG-EID' as "xmlns",

                            'https://infocentre.gsm.org/TADIG-GEN' as "xmlns:tadig-gen",

                            'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi",

                            'https://infocentre.gsm.org/TADIG-EID ./tadig-eid-10.0.xsd' as "xsi:schemaLocation"

                            )

                            -- EIDFileHeader

                            , xmlelement("EIDFileHeader"

                            , xmlelement("EIDFilePrefix", 'TI')

                            , xmlelement("EIDFileSender", 'CHEFA')

                            , xmlelement("EIDFileRecipient", 'DEUXY')

                            , xmlelement("EIDFileSeqNo", d.object_id)

                            , xmlelement("EIDFileCreationTimestamp", systimestamp)

                            , xmlelement("TADIGEidSchemaVersion", '10.0')

                            )

                            -- InvoiceList

                            , xmlelement("InvoiceList"

                            -- ----------------------------

                            -- Invoice

                            -- ----------------------------

                            , ( SELECT xmlagg (xmlelement("Invoice"

                            , xmlelement("InvSeqNo", rownum)

                            -- InvoiceMainSection

                            , xmlelement("InvoiceMainSection"

                            -- ----------------------------

                            -- InvoicingPartyInfo

                            -- ----------------------------

                            , xmlelement("InvoicingPartyInfo"

                            , xmlelement("CompanyName", d.object_name)

                            -- SenderAddress

                            , xmlelement("SenderAddress"

                            , xmlelement("tadig-gen:AddressLine", 'c/o Financial Services AG')

                            , xmlelement("tadig-gen:AddressLine", 'Kramgasse 13')

                            , xmlelement("tadig-gen:AddressLine", 'CH-3000 Bern 22')

                            , xmlelement("tadig-gen:AddressLine", 'Switzerland')

                            )

                            , xmlelement("TADIG-Code", 'USAYY')

                            -- ContactPointInfo

                            , xmlelement("ContactPointInfo"

                            , xmlelement("CP-DescriptionLine", 'This Invoice for Roaming Traffic is issued as part of the FC Service')

                            , xmlelement("CP-DescriptionLine", 'For additional information, please feel free to contact our Financial Clearing Department.')

                            , xmlelement("CP-DescriptionLine", 'Finanical Services AG - Kramgasse 13, CH-3000 Bern 22, Switzerland')

                            , xmlelement("CP-DescriptionLine", '(Phone: +41 31 999 9999, Fax: +41 31 999 9998, E-mail: financial.clearing@fcservice.com)')

                            )

                            ) -- InvoicingPartyInfo

                            -- ----------------------------

                            -- InvoicedPartyInfo

                            -- ----------------------------

                            , xmlelement("InvoicedPartyInfo"

                            , xmlelement("CompanyName", d.object_name)

                            -- DeliveryAddress

                            , xmlelement("DeliveryAddress"

                            , xmlelement("tadig-gen:AddressLine", 'c/o Ultimate Clearing AG')

                            , xmlelement("tadig-gen:AddressLine", 'Kaiserstrasse 55')

                            , xmlelement("tadig-gen:AddressLine", '18 Mansell Street')

                            , xmlelement("tadig-gen:AddressLine", '30161 Hannover-Mitte')

                            , xmlelement("tadig-gen:AddressLine", 'Germany')

                            )

                            , xmlelement("TADIG-Code", 'DNKSW')

                            -- CompanyRegistrationInfo

                            , xmlelement("CompanyRegistrationInfo"

                            , xmlelement("tadig-gen:TaxRegistrationNumber", d.object_id)

                            )

                            ) -- InvoicedPartyInfo

                            -- ----------------------------

                            -- InvoiceDetails

                            -- ----------------------------

                            , xmlelement("InvoiceDetails"

                            , xmlelement("InvoiceType", d.object_type)

                            , xmlelement("InvoiceDate", sysdate)

                            , xmlelement("InvoiceNumber", d.object_id)

                            -- InvoicePeriodDates

                            , xmlelement("InvoicePeriodDates"

                            , xmlelement("StartDate", sysdate)

                            , xmlelement("EndDate", sysdate)

                            )

                            ) -- InvoiceDetails

                            -- ----------------------------

                            -- InvoiceCurrencies

                            -- ----------------------------

                            , xmlelement("InvoiceCurrencies"

                            , xmlelement("TC-TAPCurrency", 'SDR')

                            , xmlelement("LC-LocalCurrency", 'USD')

                            , xmlelement("PC-PaymentCurrency", 'USD')

                            ) -- InvoiceCurrencies

                            -- ----------------------------

                            -- InvoiceItems

                            -- ----------------------------

                            , xmlelement("InvoiceItems"

                            -- TotalOriginalAmounts

                            , xmlelement("TotalOriginalAmounts"

                            , xmlelement("TC-Amount", d.object_id)

                            , xmlelement("LC-Amount", d.object_id)

                            , xmlelement("PC-Amount", d.object_id)

                            )

                            -- TotalDiscountAmounts

                            , xmlelement("TotalDiscountAmounts"

                            , xmlelement("TC-Amount", d.object_id)

                            , xmlelement("LC-Amount", d.object_id)

                            , xmlelement("PC-Amount", d.object_id)

                            )

                            -- TotalNetAmounts

                            , xmlelement("TotalNetAmounts"

                            , xmlelement("TC-Amount", d.object_id)

                            , xmlelement("LC-Amount", d.object_id)

                            , xmlelement("PC-Amount", d.object_id)

                            )

                            -- TotalTaxAmounts

                            , xmlelement("TotalTaxAmounts"

                            , xmlelement("TC-Amount", d.object_id)

                            , xmlelement("LC-Amount", d.object_id)

                            , xmlelement("PC-Amount", d.object_id)

                            )

                            -- TotalGrossAmounts

                            , xmlelement("TotalGrossAmounts"

                            , xmlelement("TC-Amount", d.object_id)

                            , xmlelement("LC-Amount", d.object_id)

                            , xmlelement("PC-Amount", d.object_id)

                            )

                            -- TotalRoundingAmounts

                            , xmlelement("TotalRoundingAmounts"

                            , xmlelement("TC-Amount", d.object_id)

                            , xmlelement("LC-Amount", d.object_id)

                            , xmlelement("PC-Amount", d.object_id)

                            )

                            -- TotalDueAmounts

                            , xmlelement("TotalDueAmounts"

                            , xmlelement("TC-Amount", d.object_id)

                            , xmlelement("LC-Amount", d.object_id)

                            , xmlelement("PC-Amount", d.object_id)

                            )

                            ) -- InvoiceItems

                            -- ----------------------------

                            -- ExRateList

                            -- ----------------------------

                            , xmlelement("ExRateList"

                            -- ExRateItem

                            , xmlelement("ExRateItem"

                            , xmlelement("tadig-gen:SourceCurrCode", 'SDR')

                            , xmlelement("tadig-gen:DestCurrCode", 'USD')

                            , xmlelement("tadig-gen:ExRateDate", sysdate)

                            , xmlelement("tadig-gen:ExRateValue", d.object_id)

                            )

                            ) -- ExRateList

                            -- ----------------------------

                            -- SettlementInfo

                            -- ----------------------------

                            , xmlelement("SettlementInfo"

                            , xmlelement("TypeOfSettlement", d.object_name)

                            , xmlelement("DueDate", sysdate)

                            ) -- SettlementInfo

                            -- ----------------------------

                            -- PaymentInfo

                            -- ----------------------------

                            , xmlelement("PaymentInfo"

                            , xmlelement("tadig-gen:BankAccountInfo"

                            , xmlelement("tadig-gen:BankName", 'SBU Suisse')

                            , xmlelement("tadig-gen:BankCode", 'CREXXXEDZZ80A')

                            , xmlelement("tadig-gen:BankAccountName", 'Financial Services AG')

                            , xmlelement("tadig-gen:MainBankAccountNo"

                            , xmlelement("tadig-gen:AccountNo", 'CH943048353423348591001')

                            , xmlelement("tadig-gen:AccountNoFormat", 'IBAN')

                            )

                            )

                            ) -- PaymentInfo

                            ) -- InvoiceMainSection

                            , xmlelement("RoamingTrafficDetailsList"

                            , xmlelement("RoamingTrafficDetails"

                            , xmlelement("RoamingConnectionNo", '1')

                            , xmlelement("VPMN-TADIG-Code", 'USAYY')

                            , xmlelement("HPMN-TADIG-Code", 'DNKSW')

                            -- ----------------------------

                            -- RTD-ItemList

                            -- ----------------------------

                            , xmlelement("RTD-ItemList"

                            , (SELECT xmlagg(xmlelement("RTD-Item"

                            , xmlelement("TAPFileName", nt.object_name)

                            , xmlelement("FileAction", ltrim(nt.object_type))

                            , xmlelement("TC-FileAmounts"

                            , xmlelement("TC-AmountBeforeTaxes", rownum)

                            , xmlelement("TC-Taxes", rownum)

                            , xmlelement("TC-AmountAfterTaxes", 0.00)

                            )

                            )

                            )

                            FROM ALL_OBJECTS nt

                            WHERE rownum <= 50

                            )

                            ) -- RTD-ItemList

                            , xmlelement("TC-TotalRCAmounts"

                            , xmlelement("TC-TotalRCAmountBeforeTaxes", rownum)

                            , xmlelement("TC-TotalRCTaxes", rownum)

                            , xmlelement("TC-TotalRCAmountAfterTaxes", rownum)

                            )

                            ) -- RoamingTrafficDetails

                            ) -- RoamingTrafficDetailsList

                            )

                            ) FROM ALL_OBJECTS ao

                            WHERE ROWNUM <= 10

                            ) -- Invoice

                            ) -- InvoiceList

                            ) -- TADIGEID

                            ) as CLOB

                            -- Do not use indentation in real environments

                            -- Pretty Print output is for debug only

                            INDENT size=1 ) as "XMLOUTPUT"

                          from user_objects d

                          where rownum = 1

                          order by d.object_id

                          ;

                           

                          spool off

                          exit

                          1 2 Previous Next