- Code: Select all
<?xml version="1.0" encoding="UTF-8"?>
<orders>
<singleOrder no="1" date="2015-01-22">
<customer id="1" />
<products>
<product>
<productId>1</productId>
<orderId>1</orderId>
<quantity>3</quantity>
</product>
</products>
</singleOrder>
<singleOrder no="4" date="2015-02-27">
<customer id="1" />
<products>
<product>
<productId>3</productId>
<orderId>4</orderId>
<quantity>2</quantity>
</product>
<product>
<productId>4</productId>
<orderId>4</orderId>
<quantity>4</quantity>
</product>
</products>
</singleOrder>
</orders>
I inserted this xml to xml_orders table - whole in one table.
When i make query like this:
- Code: Select all
SELECT XMLSERIALIZE(
XMLQUERY('//productId/text()' PASSING BY REF xml_col RETURNING SEQUENCE EMPTY ON
EMPTY) AS VARCHAR(10)
)AS ProductId
FROM xml_orders
I'm getting such a result: 134
How could I get every productId result in new row?