How to display/update data of tags of Oracle SQL in Python?-CodePudding

I have a table. Data in the TR_data column is listed as <Name>blahblahblah><ServerName>Blahblahblah><Path>1111</Path>.

Right now I have this.

print(f"Which transfer type do you want to see? \n (1) - ServerName \n (2) - Path")
choice_type = int(input("Enter transfer type: "))
if choice_type == 1:
   query = "SELECT tr_data FROM blahblahblah.transport WHERE tr_type = '51' AND tr_data LIKE '%<ServerName>%' AND tr_data LIKE '%</ServerName>%'"
        df = pd.read_sql(query, connection)
        print(df)

This just prints out the entire data out. However, I want it to just print out the tag of and update whatever it is in the tag.

CodePudding user response：

First, as a side note, the example XML you posted in your comment is invalid, since it's missing </SSH></SFTP.Outbound> at the end. I'm going to assume for these examples that it was just a typo.

I think the easiest way to parse/read/update the XML data in your Oracle table using Python is to do all the work on the Python end.

... your code ...

import xml.etree.ElementTree as ET
root = ET.fromstring(df.tr_data[0]) # 0 = first row only
sn = root.find("./SSH/Servers/ServerName")
print(f"Server Name: {sn.text}")
sn.text = "example1"  # update the tag text
updatedXml = ET.tostring(root)   # save the XML back to a string

sql = "update blahblahblah.transport set tr_data = :tr_data where tr_type = :tr_type"
connection.execute(sql, tr_type = '51', tr_data=updatedXml)

EDIT: For dealing with multiple rows in your DF, you'll want to apply this logic to each row. Creating a new column is probably easiest

df["ServerName"] = df.apply(lambda x: ET.fromstring(x.tr_data).find("./SSH/Servers/ServerName").text, axis=1)

And to deal with multiple ServerNames in each row, you'd need findall() which returns a list

df["ServerNames"] = df.apply(lambda x: [sn.text for sn in ET.fromstring(x.tr_data).findall("./SSH/Servers/ServerName")], axis=1)

See if those help?

You could also do this from the Oracle end, but it's more complicated. You'd need to query the XML using XMLQUERY, XMLTABLE, or the deprecated EXTRACTVALUE, then write it back to the table using XQuery Update. The SQL queries you'd need to run would look something like this:

-- read the value
select tr_type, 
    XMLQUERY('/SFTP.Outbound/SSH/Servers/ServerName/text()' passing xmltype(tr_data) returning content) as x
from transport
where tr_type = :tr_type

-- update the value
update transport set tr_data =
xmlquery(
'copy $t := $x modify(
  (for $i in $t/SFTP.Outbound/SSH/Servers/ServerName
   return replace value of node $i with $s)
) return $t'
passing tr_data as "x", :server_name as "s" returning content
)
where tr_type = :tr_type