How to extract metadata from SDE tables

[ora11gora11g]

Hello,

I'm looking for a way to extract metadata from the SDE tables (oracle 11g, AG Desktop 10.1).
I think metadata are stored in the system table GDB_ITEMS (field DOCUMENTATION).

But how to extract metadata from this table?

Thank you.

[NidhinKarthikeyan]

import arcpy, sys
from xml.etree.ElementTree import ElementTree
from arcpy import env
env.overwriteOutput = True
env.workspace = r"D:\Lakeland_GIS\GDB\Lakeland\Lakeland.gdb\AddPoints"

AGSHOME = arcpy.GetInstallInfo("Desktop")["InstallDir"]
translatorpath = AGSHOME + r"Metadata\Translator\ARCGIS2FGDC.xml" 
xmlfile = r"U:\my docs\GIS Projects\Python\Scripts\List Metadata\working_test.xml" 
  
# list any standalone feature classes
fcList = arcpy.ListFeatureClasses()
fcList.sort()
for fc in fcList:
    arcpy.ExportMetadata_conversion(fc, translatorpath, xmlfile) 
    tree = ElementTree() 
    tree.parse(xmlfile) 
    spot = tree.find("idinfo/descript/purpose") 
    print fc
    print spot.text
        
# list feature datasets
datasetList = arcpy.ListDatasets()
datasetList.sort()
for dataset in datasetList:
    print dataset
    # list feature classes inside the dataset
    fcList = arcpy.ListFeatureClasses("","",dataset)
    fcList.sort()
    for fc in fcList:
        arcpy.ExportMetadata_conversion(fc, translatorpath, xmlfile) 
        tree = ElementTree() 
        tree.parse(xmlfile) 
        spot = tree.find("idinfo/descript/purpose") 
        print "  " + fc
        print "  " + spot.text

[ora11gora11g]

Thank you Nidhinkn for your help but this is not exactly what I want.
I work on a SDE gdb and not personal or file gdb.

I want to pass a SQL command, something like Select DOCUMENTATION from gdb_items_vw, to retrieve all the information about the metadata of the layer.
But the difficulty is that this DOCUMENTATION field is CLOB type.

I found this command that seems correct:
SQL> select EXTRACT (XMLType (DOCUMENTATION), '/ metadata') as DOCUMENTATION from sde.gdb_items_vw where name = 'user_name.table_name';

... but as my sde.gdb_items_vw table is empty, the query gives me 0 selected lines!

So why this table is empty? Is that the right table to get metadata?
Thank you.

[EokNgo]

According to ESRI documentation:
"In geodatabases in Oracle and Informix, the XML columns use ArcSDE XML,  which stores information as BLOBs in a series of separate tables. As  such, they cannot be directly accessed with SQL."
http://resources.arcgis.com/en/help/main/10.2/index.html#/A_quick_tour_of_geodatabase_system_tables/...

As such, the XML metadata cannot be accessed directly by a simple query to the database.
(I tried to hunt for this as you have in the past.)

I suggest using a Python script/Model to fully export the metadata.
Use the XSLT Transformation tool and with an identity XSLT to extract all the metadata.
You can save the code below as a XLST document to feed into the tool.
See: http://www.usingxml.com/Transforms/XslIdentity

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0"
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

   <!-- IdentityTransform -->
   <xsl:template match="/ | @* | node()">
         <xsl:copy>
               <xsl:apply-templates select="@* | node()" />
         </xsl:copy>
   </xsl:template>

</xsl:stylesheet>

[ForrestJones]

According to ESRI documentation:
"In geodatabases in Oracle and Informix, the XML columns use ArcSDE XML,  which stores information as BLOBs in a series of separate tables. As  such, they cannot be directly accessed with SQL."
http://resources.arcgis.com/en/help/main/10.2/index.html#/A_quick_tour_of_geodatabase_system_tables/...

As such, the XML metadata cannot be accessed directly by a simple query to the database.
(I tried to hunt for this as you have in the past.)

I suggest using a Python script/Model to fully export the metadata.
Use the XSLT Transformation tool and with an identity XSLT to extract all the metadata.
You can save the code below as a XLST document to feed into the tool.
See: http://www.usingxml.com/Transforms/XslIdentity

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0"
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

   <!-- IdentityTransform -->
   <xsl:template match="/ | @* | node()">
         <xsl:copy>
               <xsl:apply-templates select="@* | node()" />
         </xsl:copy>
   </xsl:template>

</xsl:stylesheet>

In continuing the quote from the same documentation link (first one) above it also says you can query the GDB_Items_vw, etc.:

"To allow you to view the contents of the XML columns in the system tables in geodatabases in Oracle, two system views exist that store the contents from the geodatabase system table XML columns in a CLOB data type. The GDB_Items_vw shows the contents of the Definition, Documentation, and ItemInfo columns from the GDB_Items table in CLOB columns. The GDB_ItemRelationships_vw shows the contents of the Attributes column of the GDB_ItemRelationships table in a CLOB column. The contents of the CLOB columns can be read as text.

When querying these views, you extract the string from the CLOB column, convert it to an XML type, and execute an XPath query on it. Be aware that if you attempt to create an XML type for a column that contains a blank string, it will fail with the message "XML parsing failed" because Oracle does not support this.

To query the contents of the CLOB columns, you must configure the Oracle server to accept SQL connections. See Configuring the Oracle listener to use SQL for more information."

And some examples are available here in the doc as well. For example:

Example: Finding domain owners using SQL

Maybe you are looking for the information in the "DEFINITION" column rather than the "DOCUMENTATION" column?

[MarcoBoeringa]

I want to pass a SQL command, something like Select DOCUMENTATION from gdb_items_vw, to retrieve all the information about the metadata of the layer.
But the difficulty is that this DOCUMENTATION field is CLOB type.

I found this command that seems correct:
SQL> select EXTRACT (XMLType (DOCUMENTATION), '/ metadata') as DOCUMENTATION from sde.gdb_items_vw where name = 'user_name.table_name';

... but as my sde.gdb_items_vw table is empty, the query gives me 0 selected lines!

So why this table is empty? Is that the right table to get metadata?

Did you actually define / write metadata for the layers in your geodatabase??, or are you just interested in general Feature Class properties like extent, fields, projection etc., and have falsely assumed that ArcGIS automatically stores these as ESRI/ISO XML metadata in the gdb_items table as well? This is not the case, you need to, at the very least, activate the Metadata TAB in ArcCatalog once to have it write XML metadata to the gdb_item's metadata column (named "Documentation") for the selected Feature Class. Otherwise, the Documentation field will stay empty (Null) for the particular Feature Class you tried to query.

All the other automatically maintained system data, like the Feature Class properties, is stored elsewhere in the geodatabase system tables.

Other options:
- Did you have a look at the Name column in the gdb_items tables. Is it really enough to just specify 'user_name.table_name'? E.g. in SQL Server, you need to write 'database_name.user_name.table_name' since SQL Server supports multiple databases per instance. But maybe you need to add 'instance_name' in Oracle? (I can't check it for you, since I don't have it running).
- Some issue with the gdb_items_vw database view or the ArcSDE / Geodatabase repository. If that is the case, it starts to look like you may need to make an official support call for this with ESRI.

And some examples are available here in the doc as well. For example:

Example: Finding domain owners using SQL

More detailed instructions can be found here:

XML column queries

[AprilWilliford]

Are your layers registered with the geodatabase? They will need to be to work with gdb_items_vw.

Here is a code snippet for an example of extracting the xml metadata with Java from Oracle/ArcSDE 10.2. You don't have to use two stylesheets...just depends on your requirements.

            String strLayer = layerName;
            String connectionURL = null;
            String connectionID = null;
            String connectionPW = null;
            String driverString = null;
            connectionURL = "jdbc:oracle:thin:@server.domain:port:instance";

            connectionID = "username";
            connectionPW = "password";
            driverString = "oracle.jdbc.driver.OracleDriver";
            Class.forName(driverString);

            sqlconn = java.sql.DriverManager.getConnection(connectionURL, connectionID, connectionPW);

            stmt = sqlconn.createStatement();
            rset = stmt.executeQuery("SELECT uuid, documentation FROM sde.gdb_items_vw where upper(name) = '" + strLayer + "'");
            rset.next();
            java.io.InputStream xmlStream = rset.getBinaryStream("documentation");
            if (xmlStream != null) {
                URL url1 = new URL(<URL to ArcGIS2FGDC.xsl>);
                InputStreamReader isr1 = new InputStreamReader(url1.openStream());
                StreamSource xslSource1 = new StreamSource(isr1);
                
                TransformerFactory tFactory1 = TransformerFactory.newInstance();
                Transformer transformer1 = tFactory1.newTransformer(xslSource1);
                
                Source schemaSource = new StreamSource(xmlStream);
                StringWriter writer = new StringWriter();
                StreamResult result = new StreamResult(writer);
                transformer1.transform(schemaSource, result);

                StringReader reader2 = new StringReader(writer.toString());
                 writer2 = new StringWriter();

                URL url = new URL(<URL to your own xsl>);
                InputStreamReader isr2 = new InputStreamReader(url.openStream());
                StreamSource xslSource = new StreamSource(isr2);

                TransformerFactory tFactory = TransformerFactory.newInstance();
                Transformer transformer2 = tFactory.newTransformer(xslSource);

                transformer2.transform(new javax.xml.transform.stream.StreamSource(reader2),
                                       new javax.xml.transform.stream.StreamResult(writer2));
               
            }
            xmlStream.close();
            rset.close();
            sqlconn.close();
            return(writer2.toString());