In the hybrid storage technique, you store the complete XML document in a SQL column and, at the same time, store elements of that document in separate columns. This technique often balances the advantages and disadvantages of element and document storage.
“Using the document storage technique” demonstrates how to store the entire XML order document in the single column order_docs.order_doc. Using document storage, you must reference and access the CustomerId element in this way:
select order_doc>>getOrderElement(“CustomerID”) from order_docs where order_doc>>getOrderElement(“CustomerID”) > “222”
To access CustomerId more quickly and conveniently than with the method call, but without first translating the Order into SQL rows and columns:
Add a column to the order_docs table for the customer_id:
alter table order_docs add customer_id varchar(5) null
Update that new column with extracted customerId values.
update order_docs
set customer_id =
order_doc>>getOrderElement("CustomerId")
Reference CustomerId values directly:
select customer_id from order_docs where customer_id > “222”
You can also define an index on the column.
This
technique does not synchronize the extracted customer_id column with
the CustomerId element of the order_doc column
if you update either value.
