Hi Team,
I need to create a stored procedure which will take below mentioned XML as an input and display the ITEMNO using DBMS_OUTPUT_PUT_LINE statement.
INPUT XML:
<column name="EVENTINFO" type="xml-fragment">
<ORDER type="xml-fragment">
<NUMBER>0017700033018</NUMBER>
<DATE>05/01/2017</DATE>
<STATUS>NEW</STATUS>
<SUBTOTAL>$69.98</SUBTOTAL>
<ORDERDISCOUNT>$0.00</ORDERDISCOUNT>
<SHIPPING>$17.95</SHIPPING>
<SHIPPINGDISCOUNT>$17.95</SHIPPINGDISCOUNT>
<TAX>$0.90</TAX>
<PROMOTIONS>$0.00</PROMOTIONS>
<TOTAL>$70.88</TOTAL>
</ORDER>
<BILLINGADDRESS type="xml-fragment">
<FIRSTNAME>Patti</FIRSTNAME>
<LASTNAME>Kelly</LASTNAME>
<ADDRESS1>3328 E. Farrin Avenue</ADDRESS1>
<ADDRESS2/>
<CITY>Fresno</CITY>
<STATE>CA</STATE>
<ZIP>93726</ZIP>
<COUNTRY>United States</COUNTRY>
<PHONENUMBER>559-229-5567</PHONENUMBER>
</BILLINGADDRESS>
<SHIPPINGADDRESS type="xml-fragment">
<FIRSTNAME>Patti</FIRSTNAME>
<LASTNAME>Kelly</LASTNAME>
<ADDRESS1>3328 E. Farrin Avenue</ADDRESS1>
<ADDRESS2/>
<CITY>Fresno</CITY>
<STATE>CA</STATE>
<ZIP>93726</ZIP>
<COUNTRY>United States</COUNTRY>
<PHONENUMBER>559-229-5567</PHONENUMBER>
</SHIPPINGADDRESS>
<PAYMENTMETHOD type="xml">
<mv>
<cb>_PAYMENTMETHODCONTENT__</cb>
<row>
<col name="MERCHANT">Visa</col>
<col name="FIRSTNAME">Patti L.</col>
<col name="CARDNUMBER">************8504</col>
<col name="TYPE">CREDIT_CARD</col>
<col name="LASTNAME">Kelly</col>
</row>
</mv>
</PAYMENTMETHOD>
<ORDERITEMS type="xml">
<mv>
<cb>_ORDERITEMSCONTENT__</cb>
<row>
<col name="ITEMNO">35656</col>
<col name="ORIGINALPRICE">$34.99</col>
<col name="ITEMQTY">1</col>
<col name="TOTAL">$34.99</col>
<col name="SALEPRICE">$34.99</col>
<col name="COLOR"/>
<col name="SIZE"/>
<col name="ITEMDESCRIPTION">Premium No Iron Khaki Sand 33 30</col>
</row>
<row>
<col name="ITEMNO">35726</col>
<col name="ORIGINALPRICE">$34.99</col>
<col name="ITEMQTY">1</col>
<col name="TOTAL">$34.99</col>
<col name="SALEPRICE">$34.99</col>
<col name="COLOR"/>
<col name="SIZE"/>
<col name="ITEMDESCRIPTION">Premium No Iron Khaki British Khaki 33 30</col>
</row>
</mv>
</ORDERITEMS>
</column>
OUTPUT:
35656
35726
Can you please help me out to create this procedure. Thanks in advance.
Regards,
Rajesh
SQL> set serverout on
SQL> declare
2 c xmltype := xmltype('
3 <column name="EVENTINFO" type="xml-fragment">
4 <ORDER type="xml-fragment">
5 <NUMBER>0017700033018</NUMBER>
6 <DATE>05/01/2017</DATE>
7 <STATUS>NEW</STATUS>
8 <SUBTOTAL>$69.98</SUBTOTAL>
9 <ORDERDISCOUNT>$0.00</ORDERDISCOUNT>
10 <SHIPPING>$17.95</SHIPPING>
11 <SHIPPINGDISCOUNT>$17.95</SHIPPINGDISCOUNT>
12 <TAX>$0.90</TAX>
13 <PROMOTIONS>$0.00</PROMOTIONS>
14 <TOTAL>$70.88</TOTAL>
15 </ORDER>
16 <BILLINGADDRESS type="xml-fragment">
17 <FIRSTNAME>Patti</FIRSTNAME>
18 <LASTNAME>Kelly</LASTNAME>
19 <ADDRESS1>3328 E. Farrin Avenue</ADDRESS1>
20 <ADDRESS2/>
21 <CITY>Fresno</CITY>
22 <STATE>CA</STATE>
23 <ZIP>93726</ZIP>
24 <COUNTRY>United States</COUNTRY>
25 <PHONENUMBER>559-229-5567</PHONENUMBER>
26 </BILLINGADDRESS>
27 <SHIPPINGADDRESS type="xml-fragment">
28 <FIRSTNAME>Patti</FIRSTNAME>
29 <LASTNAME>Kelly</LASTNAME>
30 <ADDRESS1>3328 E. Farrin Avenue</ADDRESS1>
31 <ADDRESS2/>
32 <CITY>Fresno</CITY>
33 <STATE>CA</STATE>
34 <ZIP>93726</ZIP>
35 <COUNTRY>United States</COUNTRY>
36 <PHONENUMBER>559-229-5567</PHONENUMBER>
37 </SHIPPINGADDRESS>
38 <PAYMENTMETHOD type="xml">
39 <mv>
40 <cb>_PAYMENTMETHODCONTENT__</cb>
41 <row>
42 <col name="MERCHANT">Visa</col>
43 <col name="FIRSTNAME">Patti L.</col>
44 <col name="CARDNUMBER">************8504</col>
45 <col name="TYPE">CREDIT_CARD</col>
46 <col name="LASTNAME">Kelly</col>
47 </row>
48 </mv>
49 </PAYMENTMETHOD>
50 <ORDERITEMS type="xml">
51 <mv>
52 <cb>_ORDERITEMSCONTENT__</cb>
53 <row>
54 <col name="ITEMNO">35656</col>
55 <col name="ORIGINALPRICE">$34.99</col>
56 <col name="ITEMQTY">1</col>
57 <col name="TOTAL">$34.99</col>
58 <col name="SALEPRICE">$34.99</col>
59 <col name="COLOR"/>
60 <col name="SIZE"/>
61 <col name="ITEMDESCRIPTION">Premium No Iron Khaki Sand 33 30</col>
62 </row>
63 <row>
64 <col name="ITEMNO">35726</col>
65 <col name="ORIGINALPRICE">$34.99</col>
66 <col name="ITEMQTY">1</col>
67 <col name="TOTAL">$34.99</col>
68 <col name="SALEPRICE">$34.99</col>
69 <col name="COLOR"/>
70 <col name="SIZE"/>
71 <col name="ITEMDESCRIPTION">Premium No Iron Khaki British Khaki 33 30</col>
72 </row>
73 </mv>
74 </ORDERITEMS>
75 </column>');
76 begin
77 for i in (
78 SELECT xt.* FROM
79 XMLTABLE('/column/ORDERITEMS/mv/row/col'
80 PASSING c
81 COLUMNS
82 "ATTR1" VARCHAR2(80) PATH '/',
83 "ATTR2" VARCHAR2(80) PATH '@name'
84 ) xt
85 where attr2 = 'ITEMNO'
86 ) loop
87 dbms_output.put_line(i.ATTR1);
88 end loop;
89 end;
90 /
35656
35726
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>