ebaiello

Updating Metadata XML via SQL - SDE V10.1

Discussion created by ebaiello on Mar 10, 2014
Hello,

As part of our ETL processes, we perform some string manipulation to update the Publication date (xml pub_date tag) in our current SDE version 9.3 sde.gdb_usermetadata tables. See sql below.

We are in the process of upgrading to SDE 10.1 and the sde system tables are organized differently. It appears that the metadata information is only accessible via the GDB_Items_VW and is not updateable. Does anyone have any ideas on how I could implement the same string manipulation from 9.3 (below) in our new 10.1 SDE environ?

Thanks,

Eric

http://resources.arcgis.com/en/help/main/10.1/index.html#/XML_column_queries/006z000000tn000000/

-------------------
SDE 9.3 process

update sde.gdb_usermetadata set xml = (
select S_USA.clob2blob(convxml) from (
SELECT replace(CLOBXML,substr(CLOBXML,instr(CLOBXML,'<pubDate>'),
((instr(CLOBXML,'</pubDate>')+ 10) - INSTR(CLOBXML,'<pubDate>'))
) , '<pubDate>'||to_char(sysdate,'YYYY-MM-DD')||'</pubDate>' ) convxml
FROM (select S_USA.XBLOB_TO_CLOB(XML) AS CLOBXML from sde.gdb_usermetadata
where upper(name) = 'ADMINISTRATIVEFOREST_GEN'  AND OWNER = 'S_USA')
) ) where upper(name) = 'ADMINISTRATIVEFOREST_GEN' AND OWNER = 'S_USA'; COMMIT;

Outcomes