topic Re: Xquery for searching data inside enterprise GDB in SQL server in XML data type in Geodatabase Questions

Xquery for searching data inside enterprise GDB in SQL server in XML data type

Muqit_Zoarder — Thu, 14 Jul 2016 14:16:33 GMT

Dear all, in my sql server GDB i have a table containing XML data type, where i need to search and update a particular element within all the xml files and update it. Can anyone help me please ?

With best regards

Muqit Zoarder

Re: Xquery for searching data inside enterprise GDB in SQL server in XML data type

George_Thompson — Thu, 14 Jul 2016 14:31:32 GMT

Hi Muquit,

That table may be holding the metadata for a feature class. I would not recommend modifying any of the repository tables out side of the ArcGIS tools (i.e. via SQL). Here are some links to more information.

System tables of a geodatabase in SQL Server—Help | ArcGIS for Desktop

SQL Server configuration parameters—Help | ArcGIS for Desktop

Re: Xquery for searching data inside enterprise GDB in SQL server in XML data type

Muqit_Zoarder — Thu, 14 Jul 2016 15:47:33 GMT

Hi George, thanks, but how i can find one specific data out of a xml file in documentation field? see the following xml example:

<Esri>

<SyncOnce>FALSE</SyncOnce>

<itemName Sync="TRUE">LTFX.DBO.SeehausenPolygone</itemName>

<linkage Sync="TRUE">Server=192.168.224.15; Service=sde:sqlserver:192.168.224.15\BONARES,1433; Database=LTFX; User=vasilakakis; Version=dbo.DEFAULT</linkage>

<protocol Sync="TRUE">ArcSDE Connection</protocol>

</itemLocation>

Can you please give me an example query? I want to search one xml file according to any element name like itemName

thanks

regards

Muqit

Re: Xquery for searching data inside enterprise GDB in SQL server in XML data type

George_Thompson — Thu, 14 Jul 2016 16:04:56 GMT

I do not have an example for a query for extracting the XML tag that you are looking for.

Re: Xquery for searching data inside enterprise GDB in SQL server in XML data type

ChristianWells — Sun, 12 Dec 2021 10:59:00 GMT

If you know the path, you can use the value function to gather the values:

SELECT GDB_ITEMS.NAME                                                   AS "FC_Name",
  DEFINITION.value('(/DEFeatureClassInfo/Versioned)[1]', 'varchar(20)') AS "Versioned",
  GDB_ITEMTYPES.NAME                                                    AS "GDB_Type"
FROM SDE.GDB_ITEMS
INNER JOIN SDE.GDB_ITEMTYPES
ON SDE.GDB_ITEMS.TYPE         = SDE.GDB_ITEMTYPES.UUID
WHERE SDE.GDB_ITEMTYPES.NAME IN ('Feature Class','Table')

However, as George Thompson stated, please don't make any changes to the GDB repository XML tables.

Re: Xquery for searching data inside enterprise GDB in SQL server in XML data type

ChristianWells — Fri, 15 Jul 2016 16:23:17 GMT

Muqit Zoarder if this sample is helpful, please mark the post as answered.

Re: Xquery for searching data inside enterprise GDB in SQL server in XML data type

Muqit_Zoarder — Tue, 19 Jul 2016 07:27:11 GMT

I need another help, can you please tell me if I make my enterprise GDB by using dbo but not with sde instance then it is a problem ? And what are the main differences?

Regards

Re: Xquery for searching data inside enterprise GDB in SQL server in XML data type

George_Thompson — Tue, 19 Jul 2016 11:49:23 GMT

Hi Muqit,

Here some documentation that talks about the differences of DBO vs. SDE geodatabases: A comparison of geodatabase owners in SQL Server—Help | ArcGIS for Desktop

Re: Xquery for searching data inside enterprise GDB in SQL server in XML data type

ChristianWells — Tue, 19 Jul 2016 13:03:09 GMT

In addition to George's help, this new question should be attached to a different thread so others using GeoNet for help can see this new topic.