1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
SELECT XMLQUERY('
for $v at $k in /Employees/Employee
let $r := $v/email,
$e := /Employees/Employee[@emplid>1111]
where $v/@emplid >= 2222
order by $v/firstname descending
return <emp agv="{xs:integer(fn:avg($e/age))}">{
$v/firstname,
<type>{if ($v/age>40)
then "old"
else "young"}
</type>}
</emp>
' PASSING BY VALUE xv RETURNING CONTENT) AS val
--AS val很重要，否则会因为col名太长无法显示，开始还以为是xmlquery语法有问题
FROM (SELECT
xmltype ('<Employees>
<Employee emplid="1111" type="admin">
<firstname>John</firstname>
<lastname>Watson</lastname>
<age>30</age>
<email>johnwatson@sh.com</email>
</Employee>
<Employee emplid="2222" type="admin">
<firstname>Sherlock</firstname>
<lastname>Homes</lastname>
<age>32</age>
<email>sherlock@sh.com</email>
</Employee>
<Employee emplid="3333" type="user">
<firstname>Jim</firstname>
<lastname>Moriarty</lastname>
<age>52</age>
<email>jim@sh.com</email>
</Employee>
<Employee emplid="4444" type="user">
<firstname>Mycroft</firstname>
<lastname>Holmes</lastname>
<age>41</age>
<email>mycroft@sh.com</email>
</Employee>
</Employees>') AS xv FROM dual)
--RESULT
<emp agv="41">
<firstname>Sherlock</firstname>
<type>young</type>
</emp>
<emp agv="41">
<firstname>Mycroft</firstname>
<type>old</type>
</emp>
<emp agv="41">
<firstname>Jim</firstname>
<type>old</type>
</emp>
|
1
2
3
4
5
6
7
8
9
10
11
|
UPDATE purchaseorder po
SET po.OBJECT_VALUE = XMLType(bfilename('XMLDIR','NEW-DAUSTIN-20021009123335811PDT.xml'),
nls_charset_id('AL32UTF8'))
WHERE XMLExists('$p/PurchaseOrder[Reference="DAUSTIN-20021009123335811PDT"]'
PASSING po.OBJECT_VALUE AS "p");
--RESULT:
REFERENCE LINENO DESCRIPTION
-------------------------------- ------- --------------------------------
DAUSTIN-20021009123335811PDT 1 Dead Ringers
DAUSTIN-20021009123335811PDT 2 Getrud
DAUSTIN-20021009123335811PDT 3 Branded to Kill
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
UPDATE purchaseorder po
SET po.OBJECT_VALUE =
XMLQuery('copy $i := $p1 modify
(for $j in $i/PurchaseOrder/Actions/Action[1]/User
return replace value of node $j with $p2)
return $i' PASSING po.OBJECT_VALUE AS "p1",
'SKING' AS "p2" RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING po.OBJECT_VALUE AS "p");
--RESULT:
---------------------------------
<Action>
<User>SKING</User>
</Action>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery(
'copy $i := $p1 modify
((for $j in $i/PurchaseOrder/Requestor
return replace value of node $j with $p2),
(for $j in $i/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]/@Quantity
return replace value of node $j with $p3),
(for $j in $i/PurchaseOrder/LineItems/LineItem
[Description/text()="The Unbearable Lightness Of Being"]
return replace node $j with $p4))
return $i'
PASSING OBJECT_VALUE AS "p1",
'Stephen G. King' AS "p2",
25 AS "p3",
XMLType('<LineItem ItemNumber="99">
<Part Id="786936150421" Quantity="5" UnitPrice="29.95"/>
<Description>The Rock</Description>
</LineItem>') AS "p4"
RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
--RESULT:
NAME LINEITEMS
---------------- ------------------------------------------------------------------
Stephen G. King <LineItems>
<LineItem ItemNumber="1">
<Description>The Rock</Description>
<Part Id="786936150421" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Unbearable Lightness Of Being</Description>
<Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="99">
<Description>Dead Ringers</Description>
<Part Id="715515009249" UnitPrice="39.95" Quantity="2"/>
</LineItem>
</LineItems>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery(
'copy $i := $p1 modify
((for $j in $i/PurchaseOrder/LineItems/LineItem[Part/@Id="715515009058"]/Description
return replace value of node $j with ()) ,
(for $j in $i/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]/@Quantity
return replace value of node $j with ()) ,
(for $j in $i/PurchaseOrder/LineItems/LineItem
[Description/text()= "The Unbearable Lightness Of Being"]
return replace node $j with $p2))
return $i'
PASSING OBJECT_VALUE AS "p1", NULL AS "p2"
RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
--RESULT:
NAME LINEITEMS
---------------- ----------------------------------------------------------------
Sarah J. Bell <LineItems>
<LineItem ItemNumber="1">
<Description/>
<Part Id="715515009058" UnitPrice="39.95" Quantity=""/>
</LineItem>
<LineItem/>
<LineItem ItemNumber="3">
<Description>Sisters</Description>
<Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
</LineItem>
</LineItems>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery('copy $i := $p1 modify
(for $j in $i/PurchaseOrder/LineItems
return (# ora:child-element-name LineItem #)
{insert node $p2 into $j})
return $i'
PASSING OBJECT_VALUE AS "p1",
XMLType('<LineItem ItemNumber="222">
<Description>The Harder They Come</Description>
<Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
</LineItem>') AS "p2"
RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING OBJECT_VALUE AS "p");
--RESULT:
XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[@ITEMNUMBER=222]'
---------------------------------------------------------------
<LineItem ItemNumber="222">
<Description>The Harder They Come</Description>
<Part Id="953562951413" UnitPrice="22.95" Quantity="1"/>
</LineItem>
|
1
2
3
4
5
6
7
8
|
UPDATE purchaseorder
SET OBJECT_VALUE =
XMLQuery('copy $i := $p modify
delete nodes $i/PurchaseOrder/LineItems/LineItem[@ItemNumber="222"]
return $i'
PASSING OBJECT_VALUE AS "p" RETURNING CONTENT)
WHERE XMLExists('$p/PurchaseOrder[Reference="AMCEWEN-20021009123336171PDT"]'
PASSING OBJECT_VALUE AS "p");
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
CREATE OR REPLACE VIEW purchaseorder_summary OF XMLType AS
SELECT XMLQuery('copy $i := $p1 modify
((for $j in $i/PurchaseOrder/Actions
return replace value of node $j with ()),
(for $j in $i/PurchaseOrder/ShippingInstructions
return replace value of node $j with ()),
(for $j in $i/PurchaseOrder/LineItems
return replace value of node $j with ()))
return $i'
PASSING OBJECT_VALUE AS "p1" RETURNING CONTENT)
FROM purchaseorder p;
--RESULT:
---------------------------------------------------------------------------
<PurchaseOrder
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation=
"http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
<Reference>DAUSTIN-20021009123335811PDT</Reference>
<Actions/>
<Reject/>
<Requestor>David L. Austin</Requestor>
<User>DAUSTIN</User>
<CostCenter>S30</CostCenter>
<ShippingInstructions/>
<SpecialInstructions>Courier</SpecialInstructions>
<LineItems/>
</PurchaseOrder>
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
--old ID = 12
--new ID = 13
--if new ID exists, just delete old ID
--else replace old ID with new ID
UPDATE /*+ no_xml_query_rewrite */ customer
SET cocontacts=
XMLQuery('copy $tmp := .modify(
let $cont := $tmp/customer/contacts/contact
return if(exists($cont[id=13]))
then delete nodes $cont[id=12]
else
replace value of node $cont[id=12]/id with 13)
return $tmp' passing cocontacts returning content)
WHERE coid=100711
|
Oracle XML DB
Query and Update of XML Data