для хранения в XML передается как второй параметр T0_DATE() и Т0_
DATE() возвращает дату в формате по умолчанию для базы данных (DD-M0NYY):
П SELECT
T0_DATE(
EXTRACTVALUE(xml_purchase_order, 1/purchase_order/order_date’ ),
■YYYY-MM-DD’
) AS ord_date
FROM purchase_order
WHERE purchase_order_id = 1;
0RD_DATE
17-MAY-07
Следующий запрос выбирает все продукты из xml_purchase_order как
XML, используя EXTRACT(). Обратите внимание, что использование / /
позволяет получить все продукты:
□ SELECT
EXTRACT(xml_purchase_order, ‘ /purchase_order//products’ ) xml_products
FROM purchase_order
WHERE purchase_order_id = 1;
XML_PR0DUCTS
<products>
<product>
<product_id>1</product_id>
<name>Supernova video</name>
<quantity>5</quantity>
</product>
<product>
648 Глава 17
<product_id>2</product_id>
<name>Oracle SQL book</name>
<quantity>4</quantity>
</product>
</products>
Следующий запрос выбирает товар №2 из xml_purchase_order. Обратите
внимание, что product [2] возвращает товар №2.
□ SELECT
EXTRACT(
xml_purchase_ordeг,
‘ /purchase_order/products/product[2]’
) xml_product
FROM purchase_order
WHERE purchase_order_id =1;
XML_PRODUCT
<product>
<product_id>2</product_id>
<product>Oracle SQL book</name>
<quantity>4</quantity>
</product>
Следующий запрос выбирает товар “Supernova video” из xml_purchase_
order. Обратите внимание, что имя выбираемого товара помещается в
квадратные скобки:
□ SELECT
EXTRACT(
xml_purchase_ordeг,
‘ /purchase_order/products/product[name=”Supernova video’’ ] ’
) xml_product
FROM purchase_order
WHERE purchase_order_id = 1;
XML_PRODUCT
<product>
<product_id>1</product_id>
<name>Supernova video</name>
<quantity>5</quantity>
</product>
Функция EXISTSNODE() используется для проверки существования элемента
XML. EXISTSNODE() возвращает 1, если элемент существует; в противном
случае она возвращает 0. Например, следующий запрос возвращает
строку ‘Exists’ , поскольку товар №1 существует:
□ SELECT 'Exists' AS «EXISTS»
FROM purchase_order
WHERE purchase_order_id = 1
AND EXISTSN0DE(
xml_purchase_order,
'/purchase_order/products/product[product_id=1] '
XML и база данных Oracle 649
) = 1;
EXISTS
Exists
Следующий запрос не возвращает строк, так как товар №3 не существует:
П SELECT 'Exists'
FROM purchase_order
WHERE purchase_order_id = 1
AND EXISTSNODE(
xml_purchase_order,
' /p u rch a se _ o rd e r/p ro d u c ts /p ro d u c t[p ro d u c t_ id= 3 ]’
) = 1 ;
no rows selected
Следующий запрос выбирает продукты как массив переменной длины
объектов типа XMLType, используя функцию XMLSEQUENCE(). Обратите внимание
на использование product.* для выборки всех продуктов и их элементов
XML:
□ SELECT product.*
FROM TABLE(
SELECT
XMLSEQUENCE(EXTRACT(xml_purchase_order, ‘ /purchase_order//product’ ) )
FROM purchase_order
WHERE purchase_order_id = 1
) product;
COLUMN_VALUE
<product>
<product_id>1</product_id>
<name>Supernova video</name>
<quantity>5</quantity>
</product>
<product>
<product_id>2</product_id>
<name>Oracle SQL book</name>
<quantity>4</quantity>
</product>
Следующий запрос возвращает product_id, name и quantity для товаров
в виде строк, используя функцию EXTRACTVALUE():
□ SELECT
EXTRACTVALUE(рroduct.COLUMN_VALUE, ‘ /product/product_id’ ) AS product_
id,
EXTRACTVALUE(product.COLUMN_VALUE, ‘ /product/name’ ) AS name,
EXTRACTVALUE(product.COLUMN_VALUE, ‘ /product/quantity’ ) AS quantity
FROM TABLE(
SELECT
XMLSEQUENCE(EXTRACT(xml_purchase_order, ‘ /purchase_order//product’ ))
650 Глава 17
FROM purchase_order
WHERE purchase_order_id = 1
) product;
PRODUCT_ID
PRODUCT
QUANTITY
1
Supernova video
52O
racle SQL book
4
Обновление информации в схеме XML примера
Столбцы customer_order_id, order_date, customer_name, street, city, state,
zip,phone_number и products таблицы purchase_order пустые. Данные для
этих столбцов могут быть получены из XML, хранящегося в столбце xml_
purchase_order. В данном разделе используется процедура PL/SQL с именем
update_purchase_order(), читающая XML и устанавливающая другие
столбцы соответствующим образом.
Наиболее сложной задачей update_purchase_order() является процесс
чтения информации о товарах из XML и сохранение ее в столбце вложенной
таблицы products, принадлежащей таблице purchase_order. В данной
процедуре курсор используется для чтения информации о товарах из XML,
после чего XML преобразуется в строки, используя EXTRACTVALUE(), затем
строки сохраняются во вложенной таблице.
Следующее выражение (содержащееся в сценарии xml_schema_script.
sql) создает процедуру purchase_order():
□ CREATE PROCEDURE update_purchase_order(
p_purchase_order_id IN purchase_order.purchase_order_id%TYPE
) AS
v_count INTEGER := 1;
-- объявляем вложенную таблицу для хранения информации о продуктах
v_nested_table_products t_nested_table_product :=
t_nested_table_product();
- объявляем тип для записи, представляющей информацию о продукте