3 Replies Latest reply: Jul 9, 2014 7:24 PM by Jason_(A_Non) RSS

    Reading XML from .Net Application

    e5e71bf8-d375-4e68-9c95-5427d875b0c0

      Dear Oracle Developers:

       

      I am hoping someone could give me some help, pointers or good websites, as I haven't been able to find anything to help me understand how to do the following.

       

      I have a stored procedure that is getting an XML document from our application team.  They have a C# .Net Dataset that is being converted to XML using the following line of code:

      xml = ds.GetXml();

       

      so now that we have the XML from the dataset, we are passing the XML to the stored procedure with the following line of code:

      mCommand.Parameters.Add("@XML", OracleDbType.XmlType, pXML, ParameterDirection.Input);

       

      My question is:  how do I use this XML inside my stored procedure?  I need to loop through this XML (it can be a couple, to a couple hundred elements) and process each item.  Normally, I would just use a cursor to process each record in a recordset, but I am not sure how to use XML like I would a recordset.  Is there someway to convert XML to a recordset (I wouldn't think it be that easy), and if not, how could I use XML in the way I think of a recordset, in that I would loop through it to process each "row" individually, pulling out the elements and attributes?

       

      Any help you could provide would be greatly appreciated as Google hasn't been able to answer my questions at this time.

       

      Thanks

      Andy

        • 1. Re: Reading XML from .Net Application
          Jason_(A_Non)

          What is your environment that you are trying to process the XML in?  Where the XML is generated is not such a concern as valid XML is valid XML, regardless of source.  Are you trying to process the XML in a procedure within an Oracle database?  If so what version?  Do you have a simple sample you could provide for the XML?  What are you planning to do with the data you extract from the XML?

           

          The more information you provide about what you need to do in your environment, the better answers can be provided.

          • 2. Re: Reading XML from .Net Application
            e5e71bf8-d375-4e68-9c95-5427d875b0c0

            What is your environment that you are trying to process the XML in?

            Not sure I understand the question.  We are passing XML from a .Net procedure to an Oracle stored procedure.  The development environment used to create the stored procedures is a database project inside Visual Studio

             

            Are you trying to process the XML in a procedure within an Oracle database?

            Yes.  The .Net code is passing the XML to a stored procedure on Oracle.  The Oracle version is:  11g

             

            Do you have a simple sample you could provide for the XML?  What are you planning to do with the data you extract from the XML?

            I am not sure we can provide a cut and paste sample of the XML for security reasons.  But the general format is:

            <dset>

              <dset>

                <APPROVED>0</APPROVED>

                <DOCUMENTNUMBER>DOC1</DOCUMENTNUMBER>

                <ACCT>ACCT1</ACCT>

              </dset>

              <dset> 

                <APPROVED>0</APPROVED>

                <DOCUMENTNUMBER>DOC2</DOCUMENTNUMBER>

                <ACCT>ACCT2</ACCT>

              </dset>

            </dset>

             

            What are you planning to do with the data you extract from the XML?

            We are unsure how to loop through this XML.  We know how if it would be a Cursor, but not sure how to get each element of the XML file, then each attribute inside (if we are using the terms correctly).

             

            How could we loop through so we get record ACCT1 first, and then each of the elements for that record (Approved = 0, DocumentNumber = Doc1) so we know the values and be able to act upon them and do calculations. Once that is done, move onto the second record:  ACCT2.   We need to take all of these values, look up information in the database and if the information is valid, act upon it in some fashion (insert / update).

             

            Hopefully this makes more sense.

            • 3. Re: Reading XML from .Net Application
              Jason_(A_Non)

              Look at Odie's accepted answer in

              XML-to-SQL mapping data

              for what you need to do.  It really is that simple.  Your cursor in the code will be the SELECT statement he shows.  The one change I would suggest.  If you are 11.2.0.2 or higher the temporary table only needs to be created via

              
              create global temporary table tmp_xml of xmltype;
              
              

              The reason for this is that the default storage type for XMLType columns changed in that release change from CLOB (11.2.0.1 and earlier) to securefile binary XML.  As he points out, a regular table can be used, but you would need to delete the row after you are done with it.  That is the advantage of the temporary table, that and reduced logging, for data that is transient.  The reason for storing the data into a table is that if the XML is large (in terms of size, not nodes), then Oracle performance does not drop.  If large XML is stored in a PL/SQL variable or a CLOB column, performance will decrease as the size of the XML increases.

               

              More than you probably wanted to know, but some reasons for why the given solution was suggested.  If you have questions, post what you have and it can be taken from there.