Using the hybrid storage technique

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:

  1. Add a column to the order_docs table for the customer_id:

    alter table order_docs
     	add  customer_id   varchar(5) null
  2. Update that new column with extracted customerId values.

    update order_docs
     set customer_id =
  3. Reference CustomerId values directly:

    select customer_id from order_docs 
    where customer_id > “222” 

You can also define an index on the column.

NoteThis technique does not synchronize the extracted customer_id column with the CustomerId element of the order_doc column if you update either value.