1

Topic: XML-parsing once and for all

Help ...
So

Set SERVEROUTPUT ON;
declare
l_clob clob: =
' <? xml version = "1.0" encoding = "utf-8"?>
<RS>
<R>
<C0> 7005830005669684 </C0>
<C1> the NAME Ilya Imjavich; </C1>
<C2> </C2>
<C3> 32453245345 </C3>
<C4> </C4>
<C5> 83455 </C5>
<C6> Litres </C6>
<C7> 323454338.5 </C7>
<C8> 253459.25 </C8>
<C9> 2934579.25 </C9>
<C10> 345345345345 </C10>
<C11> 54675675 </C11>
<C12> THE SAVINGS BANK </C12>
<C13> 657567656 </C13>
</R>
</RS> ';
l_str varchar2 (32000);
begin
for c in (select column_value as x from table (XMLSequence (XMLType (l_clob).extract ('/RS/R/C3 '))))
loop
l_str: = c.x.extract ('/C3/text () ').getStringVal ();
end loop;
l_str: = ltrim (l_str, ', ');
dbms_output.put_line (l_str);
end;
/

And so not

Set SERVEROUTPUT ON;
declare
l_clob clob: =
' <? xml version = "1.0" encoding = "utf-8"?>
<RS xmlns = "urn:schemas-microsoft-com:xml-analysis:rowset">
<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema" xmlns:saw-sql = "urn:saw-sql" targetNamespace = "urn:schemas-microsoft-com:xml-analysis:rowset">
<xsd:complexType name = "R">
<xsd:sequence>
<xsd:element name = "C0" type = "xsd:string" minOccurs = "0" maxOccurs = "1" saw-sql:type = "varchar" saw-sql:sqlFormula = "" 01_TRANSACTION "." D_CARD "." S_NUMBER "" saw-sql:displayFormula = "" the Card "." Card Number "" saw-sql:aggregationRule = "none" saw-sql:aggregationType = "nonAgg" saw-sql:tableHeading = "Card" saw-sql:columnHeading = "card Number" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "c2f240a89abba2cad"/>
<xsd:element name = "C1" type = "xsd:string" minOccurs = "0" maxOccurs = "1" saw-sql:type = "varchar" saw-sql:sqlFormula = "" 01_TRANSACTION "." D_CARD "." S_DRIVERS "" saw-sql:displayFormula = "" the Card "." The List of drivers "" saw-sql:aggregationRule = "none" saw-sql:aggregationType = "nonAgg" saw-sql:tableHeading = "Card" saw-sql:columnHeading = "Driver" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "ce35dbe70b2dca03a"/>
<xsd:element name = "C2" type = "xsd:string" minOccurs = "0" maxOccurs = "1" saw-sql:type = "varchar" saw-sql:sqlFormula = "" 01_TRANSACTION "." D_CARD "." S_VEHICLES "" saw-sql:displayFormula = "" the Card "." The HARDWARE List "" saw-sql:aggregationRule = "none" saw-sql:aggregationType = "nonAgg" saw-sql:tableHeading = "Card" saw-sql:columnHeading = "HARDWARE" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "cdaf197d73de8391a"/>
<xsd:element name = "C3" type = "xsd:string" minOccurs = "0" maxOccurs = "1" saw-sql:type = "varchar" saw-sql:sqlFormula = "" 01_TRANSACTION "." D_CARD "." S_COMMENT "" saw-sql:displayFormula = "" the Card "." The Comment "" saw-sql:aggregationRule = "none" saw-sql:aggregationType = "nonAgg" saw-sql:tableHeading = "Card" saw-sql:columnHeading = "the Comment of the client" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "cf62e63b9618c8aff"/>
<xsd:element name = "C4" type = "xsd:string" minOccurs = "0" maxOccurs = "1" saw-sql:type = "varchar" saw-sql:sqlFormula = "" 01_TRANSACTION "." D_CARD "." S_GROUP_NAME "" saw-sql:displayFormula = "" the Card "." Group of cards "" saw-sql:aggregationRule = "none" saw-sql:aggregationType = "nonAgg" saw-sql:tableHeading = "Card" saw-sql:columnHeading = "Group of cards" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "c5879886a4e4efc46"/>
<xsd:element name = "C5" type = "xsd:double" minOccurs = "0" maxOccurs = "1" saw-sql:type = "double" saw-sql:sqlFormula = "" 01_TRANSACTION "." F_TRANSACTION "." N_QTY "" saw-sql:displayFormula = "" Indexes "." The Amount "" saw-sql:aggregationRule = "sum" saw-sql:aggregationType = "agg" saw-sql:tableHeading = "Indexes" saw-sql:columnHeading = "Amount" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "c49a6951a5cf8c980"/>
<xsd:element name = "C6" type = "xsd:string" minOccurs = "1" maxOccurs = "1" saw-sql:type = "varchar" saw-sql:sqlFormula = "" 01_TRANSACTION "." D_PRODUCT "." S_UNITS "" saw-sql:displayFormula = "" the Product "."  "" saw-sql:aggregationRule = "none" saw-sql:aggregationType = "nonAgg" saw-sql:tableHeading = "Product" saw-sql:columnHeading = "Unit of measure" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "c16b40fd58302fb00"/>
<xsd:element name = "C7" type = "xsd:double" minOccurs = "0" maxOccurs = "1" saw-sql:type = "double" saw-sql:sqlFormula = "" 01_TRANSACTION "." F_TRANSACTION "." F_SUM_WO_DISCOUNT "" saw-sql:displayFormula = "" Indexes "." The Total of purchase without a discount "" saw-sql:aggregationRule = "sum" saw-sql:aggregationType = "agg" saw-sql:tableHeading = "Indexes" saw-sql:columnHeading = "the Total without a discount" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "ca1e05d19850b8feb"/>
<xsd:element name = "C8" type = "xsd:double" minOccurs = "0" maxOccurs = "1" saw-sql:type = "double" saw-sql:sqlFormula = "ifnull (" 01_TRANSACTION "." F_TRANSACTION "." F_DISCOUNT ", 0)" saw-sql:displayFormula = "IFNULL (" Indexes "." The Discount ", 0)" saw-sql:aggregationRule = "complex" saw-sql:aggregationType = "agg" saw-sql:tableHeading = "Indexes" saw-sql:columnHeading = "the discount Total" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "ca2c45a2fae1f92c1"/>
<xsd:element name = "C9" type = "xsd:double" minOccurs = "0" maxOccurs = "1" saw-sql:type = "double" saw-sql:sqlFormula = "" 01_TRANSACTION "." F_TRANSACTION "." F_SUM "" saw-sql:displayFormula = "" Indexes "." The purchase Total "" saw-sql:aggregationRule = "sum" saw-sql:aggregationType = "agg" saw-sql:tableHeading = "Indexes" saw-sql:columnHeading = "the Total for payment" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "c20740d6d8db13efe"/>
<xsd:element name = "C10" type = "xsd:double" minOccurs = "0" maxOccurs = "1" saw-sql:type = "double" saw-sql:sqlFormula = "" 01_TRANSACTION "." F_TRANSACTION "." F _ % _ DISCOUNT "" saw-sql:displayFormula = "" Indexes "." Discount % "" saw-sql:aggregationRule = "complex" saw-sql:aggregationType = "agg" saw-sql:tableHeading = "Indexes" saw-sql:columnHeading = "discount %" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "c7d9c7cc8e3dc8618"/>
<xsd:element name = "C11" type = "xsd:string" minOccurs = "0" maxOccurs = "1" saw-sql:type = "varchar" saw-sql:sqlFormula = "" 01_TRANSACTION "." D_AGREEMENT "." S_AGREEMENT_NUM "" saw-sql:displayFormula = "" the Contract "." Contract Number "" saw-sql:aggregationRule = "none" saw-sql:aggregationType = "nonAgg" saw-sql:tableHeading = "Contract" saw-sql:columnHeading = "contract Number" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "cd0b531d57ab026e9"/>
<xsd:element name = "C12" type = "xsd:string" minOccurs = "1" maxOccurs = "1" saw-sql:type = "varchar" saw-sql:sqlFormula = "" 01_TRANSACTION "." D_CLIENT "." S_SHORT_NAME "" saw-sql:displayFormula = "" the Client "." The Short name of the client "" saw-sql:aggregationRule = "none" saw-sql:aggregationType = "nonAgg" saw-sql:tableHeading = "Client" saw-sql:columnHeading = "the Name of the client" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "c69357768769b50ac"/>
<xsd:element name = "C13" type = "xsd:string" minOccurs = "0" maxOccurs = "1" saw-sql:type = "varchar" saw-sql:sqlFormula = "" 01_TRANSACTION "." D_CLIENT "." S_INN "" saw-sql:displayFormula = "" the Client "." An INN "" saw-sql:aggregationRule = "none" saw-sql:aggregationType = "nonAgg" saw-sql:tableHeading = "Client" saw-sql:columnHeading = "INN" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "cb0894ca4d6e6faaa"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<R>
<C0> 7005830005669684 </C0>
<C1> the NAME Ilya Imjavich; </C1>
<C2> </C2>
<C3> 32453245345 </C3>
<C4> </C4>
<C5> 83455 </C5>
<C6> Litres </C6>
<C7> 323454338.5 </C7>
<C8> 253459.25 </C8>
<C9> 2934579.25 </C9>
<C10> 345345345345 </C10>
<C11> 54675675 </C11>
<C12> THE SAVINGS BANK </C12>
<C13> 657567656 </C13>
</R>
</RS> ';
l_str varchar2 (32000);
begin
for c in (select column_value as x from table (XMLSequence (XMLType (l_clob).extract ('/RS/R/C3 '))))
loop
l_str: = c.x.extract ('/C3/text () ').getStringVal ();
end loop;
l_str: = ltrim (l_str, ', ');
dbms_output.put_line (l_str);
end;
/

2

Re: XML-parsing once and for all

Rmagistr;

select xmltype (' <? xml version = "1.0" encoding = "utf-8"?>
<RS xmlns = "urn:schemas-microsoft-com:xml-analysis:rowset">
<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema" xmlns:saw-sql = "urn:saw-sql" targetNamespace = "urn:schemas-microsoft-com:xml-analysis:rowset">
<xsd:complexType name = "R">
<xsd:sequence>
<xsd:element name = "C0" type = "xsd:string" minOccurs = "0" maxOccurs = "1" saw-sql:type = "varchar" saw-sql:sqlFormula = "" 01_TRANSACTION "." D_CARD "." S_NUMBER "" saw-sql:displayFormula = "" the Card "." Card Number "" saw-sql:aggregationRule = "none" saw-sql:aggregationType = "nonAgg" saw-sql:tableHeading = "Card" saw-sql:columnHeading = "card Number" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "c2f240a89abba2cad"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<R>
<C0> 7005830005669684 </C0>
<C1> the NAME Ilya Imjavich; </C1>
<C2/>
<C3> 32453245345 </C3>
<C4/>
<C5> 83455 </C5>
<C6> Litres </C6>
<C7> 323454338.5 </C7>
<C8> 253459.25 </C8>
<C9> 2934579.25 </C9>
<C10> 345345345345 </C10>
<C11> 54675675 </C11>
<C12> THE SAVINGS BANK </C12>
<C13> 657567656 </C13>
</R>
</RS> ') from dual;

3

Re: XML-parsing once and for all

nv.;
What you were, but a situation especially did not explain.
[quote =]

saw-sql:sqlFormula = "" 01_TRANSACTION "." D_CARD "." S_NUMBER "" saw-sql:displayFormula = "" the Card "." Card Number ""

Does not fly up.
XML not CSV, screening of inverted commas by inverted commas does not support. Can check up   .
Or at formation transform internal double quotes in and quot; (without a gap), or delete this  from a file.

set define off
select xmltype.createXML (' <? xml version = "1.0" encoding = "utf-8"?>
<RS xmlns = "urn:schemas-microsoft-com:xml-analysis:rowset">
<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema" xmlns:saw-sql = "urn:saw-sql" targetNamespace = "urn:schemas-microsoft-com:xml-analysis:rowset">
<xsd:complexType name = "R">
<xsd:sequence>
<xsd:element name = "C0" type = "xsd:string" minOccurs = "0" maxOccurs = "1" saw-sql:type = "varchar" saw-sql:sqlFormula = ""01_TRANSACTION"."D_CARD"."S_NUMBER"" saw-sql:displayFormula = """." "" saw-sql:aggregationRule = "none" saw-sql:aggregationType = "nonAgg" saw-sql:tableHeading = "Card" saw-sql:columnHeading = "card Number" saw-sql:isDoubleColumn = "false" saw-sql:columnID = "c2f240a89abba2cad"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<R>
<C0> 7005830005669684 </C0>
<C1> the NAME Ilya Imjavich; </C1>
<C2/>
<C3> 32453245345 </C3>
<C4/>
<C5> 83455 </C5>
<C6> Litres </C6>
<C7> 323454338.5 </C7>
<C8> 253459.25 </C8>
<C9> 2934579.25 </C9>
<C10> 345345345345 </C10>
<C11> 54675675 </C11>
<C12> THE SAVINGS BANK </C12>
<C13> 657567656 </C13>
</R>
</RS> ') from dual;