With the help of tech support, I am partially to my goal. Using the SQL command they supplied, I'm able to query and get the results I need in MS SQL Manager Studio
use my_database
SELECT ITEMS.Definition.value( '(/GPReplica/SibConnectionString)[1]', 'nvarchar(max)')
AS "child path" from DBO.GDB_ITEMS
AS ITEMS INNER JOIN dbo.GDB_ITEMTYPES
AS ITEMTYPES ON ITEMS .Type = ITEMTYPES .UUID
WHERE ITEMTYPES. Name = 'Replica'
pretty slick. Then looking the help ArcSDESQLExecute—Help | ArcGIS Desktop I tried to run the script within python but was getting an error " AttributeError: ArcSDESQLExecute: StreamBindOutputColumn ArcSDE Error -65"
Some browser searching brought me to a couple stackexchange (no help) and to Joshua Bixby comments How can I access a SQL Server 2012 database sequence value in arcpy which seems to suggest I need to use a cast() for my SQL statement.
This is new to me, and although I have tried many combinations, I have not found the correct location for my cast parenthesis, for lack of a better description, to get this to work. My python snippet is below.....spacing/tabs for the SQL statement might be off to in this sample. I have tried adding continuation and or stringing it out all in one line, with no luck. I think it is the "sql = " statement that is cause me problems.
setOwner = "dbo"
masterConnect = "__prod-me@wcgis_master.sde"
masterGDB = r'Database Connections\{0}'.format(masterConnect)
sde_conn = arcpy.ArcSDESQLExecute(masterGDB)
sql = '''SELECT ITEMS.Definition .value( '(/GPReplica/SibConnectionString)[1]', 'nvarchar(max)')
AS "child path" from {0}.GDB_ITEMS
AS ITEMS INNER JOIN {0}.GDB_ITEMTYPES
AS ITEMTYPES ON ITEMS .Type = ITEMTYPES .UUID
WHERE ITEMTYPES. Name = 'Replica';
'''.format(setOwner)
sde_return = sde_conn.execute(sql)
for i in sde_return:
print('{}: {}'.format(*i))
Any suggestions to get this to work?