C# how to retrieve XML stored in BLOB, then update it and store

Question

I have following problem. I am coding in C# and I need retrieve XML from BLOB and then work with it (with XML).

Part from my code

...
var xmldoc = new XmlDocument();
OracleCommand oraCmd = new OracleCommand();
oraCmd.Connection = db.oraConnection;
oraCmd.CommandText = "SELECT XML_IN_BLOB FROM TABLE_X"; 
oraCmd.CommandType = CommandType.Text;
OracleDataReader dr = oraCmd.ExecuteReader();
dr.Read();
while (dr.Read())
{
// 1. convert dr. value into XML //var xmldoc
// 2. update XML element
// 3. store/update BLOB in TABLE_X
}
db.Close();

Do you have any ideas?

If you can, change `BLOB` to `CLOB`, and work as a .Net `String`, as a common datatype `String xml_str = reader.GetValue(1)`, or if you use `OracleDataReader` as I see, you can call `reader.GetOracleLob`, goal is to get string in easy way, and after that use `XmlDocument.LoadXml(String)`. — Seyran, Apr 26 '17 at 12:38

score 0 · Answer 1 · edited May 23 '17 at 12:02

0

Examine the documentation of OracleDataReader to determine how to get a String Field out of it.
Use XmlDocument.LoadXml(String) to create an editable structure from the string
Examine the documentation of XmlDocument to determine how to change an element
Saving the xml back into a String is a little more complicated but you can follow this example.

edited May 23 '17 at 12:02

Community

1
1

answered Apr 26 '17 at 12:30

Kempeth

1,856
2
22
37

LoadXml(String) - need string as input, it means that before - you need retrieve string from BLOB. I do not know how. – Anton Lieskovsky Apr 26 '17 at 12:34
Read Seyrans comment. – manuzi1 Apr 26 '17 at 12:52

AlexDev · Answer 2 · 2017-04-26T13:37:25.737

If you are going to update the rows then you need to change your SELECT to include an Id. You will also need structure to keep all the ids and modified xml bytes.

var xmlsToUpdate = new Dictionary<int, byte[]>();
while (dr.Read())
{

    //Open it as a blob, then load the xml from the Stream.
    var id     = dr.GetInt32(0);
    var stream = dr.GetOracleLob(1);
    var xml    = new XmlDocument();
    xml.Load(stream);

    //manipulate xml

    var ms = new MemoryStream();
    xml.Save(ms);
    var bytes = ms.ToArray();
    xmlsToUpdate.Add(id, bytes);
}

After thar loop over the list and execute an update statement:

cmd.CommandText = "UPDATE TABLEX SET XML_IN_BLOB = :data WHERE id=" + item.Key;
cmd.Parameters.Add(":data", OracleType.Blob);
cmd.Parameters[":data"].Value = item.Value;

And don't forget to dispose the disposables. It might be easier to do with an ORM.

C# how to retrieve XML stored in BLOB, then update it and store

2 Answers2