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

1901
8
07-14-2016 07:16 AM
Highlighted
Occasional Contributor

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

Tags (1)
Reply
0 Kudos
8 Replies
Highlighted
Esri Frequent Contributor

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

--- George T.
Reply
0 Kudos
Highlighted
Occasional Contributor

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

<metadata xml:lang="de">

  <Esri>

    <CreaDate>20160308</CreaDate>

    <CreaTime>11184000</CreaTime>

    <ArcGISFormat>1.0</ArcGISFormat>

    <SyncOnce>FALSE</SyncOnce>

    <DataProperties>

      <itemProps>

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

        <imsContentType Sync="TRUE">002</imsContentType>

        <itemLocation>

          <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

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

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

--- George T.
Highlighted
Esri Contributor

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.

Highlighted
Esri Contributor

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

Reply
0 Kudos
Highlighted
Occasional Contributor

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

Reply
0 Kudos
Highlighted
Esri Frequent Contributor

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

--- George T.
Reply
0 Kudos
Highlighted
Esri Contributor

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.

Reply
0 Kudos