A server-side script

Using the SQL script in “Storing an XML ResultSet document in a SQL column”, you stored complete XML ResultSet documents in the rs_doc column of the resultset_docs table. The following SQL commands, executed in a server environment, reference and update the columns contained in those documents.

You can select columns by name or by number:

Select the columns of row 1, specifying columns by name:

select rs_doc>>getColumn(1, "Date"),
 	rs_doc>>getColumn(1, "CustomerId"), 
 	rs_doc>>getColumn(1, "CustomerName"),
 	rs_doc>>getColumn(1, "ItemId"), 
 	rs_doc>>getColumn(1, "ItemName"),
 	rs_doc>>getColumn(1, "Quantity"), 
 	rs_doc>>getColumn(1,  "unit")
  from resultset_docs

Select the columns of row 1, specifying columns by number:

select rs_doc>>getColumn(1, 0), 
 	rs_doc>>getColumn(1, 1),
 	rs_doc>>getColumn(1, 2),
 	rs_doc>>getColumn(1, 3), 
 	rs_doc>>getColumn(1, 4),
 	rs_doc>>getColumn(1, 5), 
 	rs_doc>>getColumn(1,  6)
 from resultset_docs

Specify some nonexisting columns and rows. Those references return null values.

Select rs_doc>>getcolumn(1, "itemid"), 
 	rs_doc>>getcolumn(1, "xxx"),
 	rs_doc>>getcolumn(1, "Quantity"), 
 	rs_doc>>getcolumn(99,  "unit"),
 	rs_doc>>getColumn(1, 876)
 from resultset_docs

Update columns in the stored ResultSet document:

update resultset_docs
set rs_doc = rs_doc>>setColumn(1, "ItemName", 																						“Wrench”)

where id= “1”

update resultset_docs
 set rs_doc = rs_doc>>setColumn(1, "ItemId", "967")
 where id=”1”

update resultset_docs
 set rs_doc = rs_doc>>setColumn(1,  "unit", "6")
 where id=”1”

select rs_doc>>getColumn(1, "ItemName"),
 	rs_doc>>getColumn(1, "ItemId"),
 	rs_doc>>getColumn(1,  "unit")
 from resultset_docs
 where id=”1”