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.
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:
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.
Look at Odie's accepted answer in
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 18.104.22.168 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 (22.214.171.124 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.