XML Parsing Failed

[ghost]

I am not sure if I am posting the issue in the right place.

I am trying to run this SQL - https://github.com/Esri/developer-support/blob/master/arcsde-sql/oracle/multi-user-management/delta-table-record-count.sql.

The following code is fails parse the XML.

SELECT items.name AS FC_Name,
XMLCast(XMLQuery('*/Versioned' PASSING xmltype(items.definition) RETURNING CONTENT) AS VARCHAR(100)) AS Versioned
FROM gdb_items_vw items 

It is throwing following error message:

ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00245: extra data after end of document Error at line 1 ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at line 1

31011. 00000 - "XML parsing failed" Cause: XML parser returned an error while trying to parse the document. Action: Check if the document to be parsed is valid.

[kgalliher]

@RajChavada Are you trying to run just that query or the entire function? The entire function succeeds for me in 10.5.1 and 10.2.2 Oracle geodatabases.

Running just the query: SELECT items.name AS FC_Name, EXTRACTVALUE(xmltype(items.definition), '*/DEFeatureClassInfo') "Versioned" FROM gdb_items_vw items

by itself will fail to find the "Versioned" tag without it's ITEMTYPES join and "Feature Class, Table" filter: INNER JOIN gdb_itemtypes itemtypes ON items.Type = itemtypes.UUID WHERE ITEMTYPES.NAME IN ('Feature Class', 'Table');

Without the filter, the XMLQuery will scan all the rows in the gdb_items_vw view which will contain null values and other xml namespaces that do not contain the Versioned tag. This is the cause of the error in this scenario.

If you just need to see what feature classes are versioned and which are not, use the full query: SELECT items.name AS FC_Name, XMLCast(XMLQuery('*/Versioned' PASSING xmltype(items.definition) RETURNING CONTENT) AS VARCHAR(100)) AS Versioned FROM gdb_items_vw items INNER JOIN gdb_itemtypes itemtypes ON items.Type = itemtypes.UUID WHERE ITEMTYPES.NAME IN ('Feature Class', 'Table');

[ghost]

@natas333x2 Thank you for your feedback. I ran the entire function first but it failed with the exact same error. We have ArcGIS 10.4.1 and 10.4.1 Oracle geodatabase. Just because the entire function failed to execute, I broke down the code to pieces to see where exactly the it was failing. Then, I realized the query mentioned below was failing to execute: SELECT items.name AS FC_Name, EXTRACTVALUE(xmltype(items.definition), '*/DEFeatureClassInfo') "Versioned" FROM gdb_items_vw items. INNER JOIN gdb_itemtypes itemtypes ON items.Type = itemtypes.UUID WHERE ITEMTYPES.NAME IN ('Feature Class', 'Table')

Eventually, I managed to run the the entire function by replacing the above part with the code shown below after discovering this example on Esri's help documentation - http://desktop.arcgis.com/en/arcmap/10.3/manage-data/using-sql-with-gdbs/determining-which-data-is-versioned.htm :

`SELECT items.name AS FC_Name
FROM gdb_items_vw items
INNER JOIN gdb_itemtypes itemtypes
    ON items.Type         = itemtypes.UUID
    WHERE ITEMTYPES.NAME IN ('Feature Class', 'Table')
AND
  ITEMS.DEFINITION  LIKE '%Versioned>true%'
`

[kgalliher]

@RajChavada Interesting, I'll have to try on a 10.4.1 geodatabase. What do you get when you run this query? SELECT items.name AS FC_Name, EXTRACTVALUE(xmltype(items.definition), '*/Versioned') as vers FROM gdb_items_vw items INNER JOIN gdb_itemtypes itemtypes ON items.Type = itemtypes.UUID WHERE ITEMTYPES.NAME IN ('Feature Class', 'Table');

Do you get the same "unexpected end-of-file" error?

[ghost]

@natas333x2 I get the following error message when I run the above query,

ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00245: extra data after end of document Error at line 1 ORA-06512: at "SYS.XMLTYPE", line 272 ORA-06512: at line 1

31011. 00000 - "XML parsing failed" Cause: XML parser returned an error while trying to parse the document. Action: Check if the document to be parsed is valid.

The following is modified version of the entire script. @natas333x2 , Since the original script works on your geodatabase, is there any way you can check the results by running the modified version of the script to confirm if you get the same results? I would really appreciate it.

SET SERVEROUTPUT ON

DECLARE

  --Create a list of all fully-registered objects and their versioning status
  CURSOR fcList
  IS
    SELECT items.name AS FC_Name
     FROM gdb_items_vw items
    INNER JOIN gdb_itemtypes itemtypes
    ON items.Type         = itemtypes.UUID
    WHERE ITEMTYPES.NAME IN ('Feature Class', 'Table')
    AND 
    items.definition LIKE '%Versioned>true%';
  SQL_STMT VARCHAR2(200); --Intermediate SQL Statement for dynamic variables
  ADDS     VARCHAR2(61); --Fully-qualified Adds table name
  DELS     VARCHAR2(61); --Fully-qualified Delets table name
  ACNT     NUMBER; --Record count of the Adds table
  dCNT     NUMBER; --Record count of the Deletes table
BEGIN
  DBMS_OUTPUT.ENABLE (100000000);
  FOR FC IN FCLIST
  --Loop through the feature class list and return delta counts on versioned objects
  LOOP
      SELECT (t.owner
        || '.A'
        || t.registration_id)
      INTO adds --Set "adds" variable to fully-qualifed adds table
      FROM table_registry t
      WHERE (t.owner
        || '.'
        || t.table_name) = upper(fc.fc_name);
      SELECT (t.owner
        || '.D'
        || t.registration_id)
      INTO dels --Set "dels" variable to the fully-qualified delete table
      FROM table_registry t
      WHERE (t.owner
        || '.'
        || T.TABLE_NAME) = UPPER(FC.FC_NAME);
      SQL_STMT          := 'select count(*) from ' || ADDS || '';
      EXECUTE IMMEDIATE SQL_STMT INTO ACNT; --Set "ACNT" variable to the count of records in the adds table
      SQL_STMT := 'select count(*) from ' || DELS || '';
      EXECUTE IMMEDIATE SQL_STMT INTO DCNT; --Set "DCNT" variable to the count of records in the delete table
      --Print the output for each versioned feature class
      DBMS_OUTPUT.PUT_LINE(rpad(upper(fc.fc_name),64,'.') || '(ADDS)' || rpad(acnt,10) || ' (DELS)' || rpad(dcnt,10));
    END LOOP;
END;

[kgalliher]

@RajChavada Your modified script works for me too. I took out the ">true" part of the AND clause and it still worked. Let me know if it works for you too. It might just be an aesthetic thing for me but I think items.definition LIKE '%Versioned%';

provides more clarity than

items.definition LIKE '%Versioned>true%';