Read data from XML file

2023-03-15 17:31 问答作者：

I have column (CLOB data type) with XML data in it. How to read data from that column and insert into myTable? This XML represent one report divided by Location. Every location has analytic data and total at the end.

How to get something like this:

bilbo bagins Total BBB (tran: 6) 12.00 13.00 14.10
bilbo bagins Total EEE (tran: 2) 12.50 44.59 72.52
bilbo bagins Total bilbo bagins (tran: 8) 34.89 17.85
...

Data in CLOB column:

<?xml version="1.0" encoding="UTF-8" ?>
<CrystalReport xmlns="urn:crystal-reports:schemas:report-detail"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:crystal-reports:schemas:report-detail http://www.businessobjects.com/products/xml/CR2008Schema.xsd">
<ReportHeader>
<Section SectionNumber="0">
</Section>
</ReportHeader>
<Group Level="1">
<GroupHeader>
<Section SectionNumber="0">
<Field Name="GroupNameLocation1" FieldName="GroupName ({SubAgentSettlement.Location})"><FormattedValue>bilbo bagins</FormattedValue><Value>bilbo bagins</Value></Field>
</Section>
</GroupHeader>
<Group Level="2">
<GroupHeader>
<Section SectionNumber="0">
</Section>
</GroupHeader>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="GroupFooterText1" FieldName="{@GroupFooterText}"><FormattedValue>Total BBB (tran: 6)</FormattedValue><Value>Total BBB (tran: 6)</Value></Field>
<Field Name="SumofCommission1" FieldName="Sum ({SubAgentSettlement.Commission}, {SubAgentSettlement.Currency})"><FormattedValue>12.00</FormattedValue><Value>12.00</Value></Field>
<Field Name="SumofCommissionBbb1" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Currency})"><FormattedValue>13.00</FormattedValue><Value>13.00</Value></Field>
<Field Name="SumofCommissionEee1" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Currency})"><FormattedValue>14.10</FormattedValue><Value>14.10</Value></Field>
</Section>
</GroupFooter>
</Group>
<Group Level="2">
<GroupHeader>
<Section SectionNumber="0">
</Section>
</GroupHeader>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="GroupFooterText1" FieldName="{@GroupFooterText}"><FormattedValue>Total EEE (tran: 2)</FormattedValue><Value>Total EEE (tran: 2)</Value></Field>
<Field Name="SumofCommission1" FieldName="Sum ({SubAgentSettlement.Commission}, {SubAgentSettlement.Currency})"><FormattedValue>12.50</FormattedValue><Value>12.50</Value></Field>
<Field Name="SumofCommissionBbb1" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Currency})"><FormattedValue>44.59</FormattedValue><Value>44.59</Value></Field>
<Field Name="SumofCommissionEee1" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Currency})"><FormattedValue>72.52</FormattedValue><Value>72.52</Value></Field>
</Section>
</GroupFooter>
</Group>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="LocationGroupFooterText1" FieldName="{@LocationGroupFooterText}"><FormattedValue>Total bilbo bagins (tran: 8)</FormattedValue><Value>Total bilbo bagins (tran: 8)</Value></Field>
<Field Name="SumofCommissionBbb2" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Location})"><FormattedValue>34.89</FormattedValue><Value>34.89</Value></Field>
<Field Name="SumofCommissionEee2" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Location})"><FormattedValue>17.85</FormattedValue><Value>17.85</Value></Field>
</Section>
</GroupFooter>
</Group>
<Group Level="1">
<GroupHeader>
<Section SectionNumber="0">
<Field Name="GroupNameLocation1" FieldName="GroupName ({SubAgentSettlement.Location})"><FormattedValue>Bruce Lee</FormattedValue><Value>Bruce Lee</Value></Field>
</Section>
</GroupHeader>
<Group Level="2">
<GroupHeader>
<Section SectionNumber="0">
</Section>
</GroupHeader>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="GroupFooterText1" FieldName="{@GroupFooterText}"><FormattedValue>Total BBB (tran: 5)</FormattedValue><Value>Total BBB (tran: 5)</Value></Field>
<Field Name="SumofCommission1" FieldName="Sum ({SubAgentSettlement.Commission}, {SubAgentSettlement.Currency})"><FormattedValue>18.11</FormattedValue><Value>18.11</Value></Field>
<Field Name="SumofCommissionBbb1" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Currency})"><FormattedValue>3.24</FormattedValue><Value>3.24</Value></Field>
<Field Name="SumofCommissionEee1" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Currency})"><FormattedValue>1.33</FormattedValue><Value>1.33</Value></Field>
</Section>
</GroupFooter>
</Group>
<Group Level="2">
<GroupHeader>
<Section SectionNumber="0">
</Section>
</GroupHeader>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="GroupFooterText1" FieldName="{@GroupFooterText}"><FormattedValue>Total EEE (tran: 8)</FormattedValue><Value>Total EEE (tran: 8)</Value></Field>
<Field Name="SumofCommission1" FieldName="Sum ({SubAgentSettlement.Commission}, {SubAgentSettlement.Currency})"><FormattedValue>10.17</FormattedValue><Value>10.17</Value></Field>
<Field Name="SumofCommissionBbb1" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Currency})"><FormattedValue>7.62</FormattedValue><Value>7.62</Value></Field>
<Field Name="SumofCommissionEee1" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Currency})"><FormattedValue>1.53</FormattedValue><Value>1.53</Value></Field>
</Section>
</GroupFooter>
</Group>
<Group Level="2">
<GroupHeader>
<Section SectionNumber="0">
</Section>
</GroupHeader>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="GroupFooterText1" FieldName="{@GroupFooterText}"><FormattedValue>Total UUU (transactions: 7)</FormattedValue><Value>Total UUU (transactions: 7)</Value></Field>
<Field Name="SumofCommission1" FieldName="Sum ({SubAgentSettlement.Commission}, {SubAgentSettlement.Currency})"><FormattedValue>2.01</FormattedValue><Value>2.01</Value></Field>
<Field Name="SumofCommissionBbb1" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Currency})"><FormattedValue>3.71</FormattedValue><Value>3.71</Value></Field>
<Field Name="SumofCommissionEee1" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Currency})"><FormattedValue>2.58</FormattedValue><Value>2.58</Value></Field>
</Section>
</GroupFooter>
</Group>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="LocationGroupFooterText1" FieldName="{@LocationGroupFooterText}"><FormattedValue>Total Bruce Lee (tran: 60)</FormattedValue><Value>Total Bruce Lee (tran: 60)</Value></Field>
<Field Name="SumofCommissionBbb2" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Location})"><FormattedValue>99.74</FormattedValue><Value>99.74</Value></Field>
<Field Name="SumofCommissionEee2" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Location})"><FormattedValue>55.81</FormattedValue><Value>55.81</Value></Field>
</Section>
</GroupFooter>
</Group>
<Group Level="1">
<GroupHeader>
<Section SectionNumber="0">
<Field Name="GroupNameLocation1" FieldName="GroupName ({SubAgentSettlement.Location})"><FormattedValue>Katar pipin</FormattedValue><Value>Katar pipin</Value></Field>
</Section>
</GroupHeader>
<Group Level="2">
<GroupHeader>
<Section SectionNumber="0">
</Section>
</GroupHeader>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="GroupFooterText1" FieldName="{@GroupFooterText}"><FormattedValue>Total BBB (tran: 5)</FormattedValue><Value>Total BBB (tran: 5)</Value></Field>
<Field Name="SumofCommission1" FieldName="Sum ({SubAgentSettlement.Commission}, {SubAgentSettlement.Currency})"><FormattedValue>7.00</FormattedValue><Value>7.00</Value></Field>
<Field Name="SumofCommissionBbb1" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Currency})"><FormattedValue>7.00</FormattedValue><Value>7.00</Value></Field>
<Field Name="SumofCommissionEee1" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Currency})"><FormattedValue>3.82</FormattedValue><Value>3.82</Value></Field>
</Section>
</GroupFooter>
</Group>
<Group Level="2">
<GroupHeader>
<Section SectionNumber="0">
</Section>
</GroupHeader>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="GroupFooterText1" FieldName="{@GroupFooterText}"><FormattedValue>Total EEE (tran: 3)</FormattedValue><Value>Total EEE (tran: 3)</Value></Field>
<Field Name="SumofCommission1" FieldName="Sum ({SubAgentSettlement.Commission}, {SubAgentSettlement.Currency})"><FormattedValue>65.50</FormattedValue><Valu开发者_StackOverflow社区e>65.50</Value></Field>
<Field Name="SumofCommissionBbb1" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Currency})"><FormattedValue>1.75</FormattedValue><Value>1.75</Value></Field>
<Field Name="SumofCommissionEee1" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Currency})"><FormattedValue>55.50</FormattedValue><Value>55.50</Value></Field>
</Section>
</GroupFooter>
</Group>
<Group Level="2">
<GroupHeader>
<Section SectionNumber="0">
</Section>
</GroupHeader>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="GroupFooterText1" FieldName="{@GroupFooterText}"><FormattedValue>Total UUU (tran: 1)</FormattedValue><Value>Total UUU (tran: 1)</Value></Field>
<Field Name="SumofCommission1" FieldName="Sum ({SubAgentSettlement.Commission}, {SubAgentSettlement.Currency})"><FormattedValue>1.00</FormattedValue><Value>1.00</Value></Field>
<Field Name="SumofCommissionBbb1" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Currency})"><FormattedValue>17.35</FormattedValue><Value>17.35</Value></Field>
<Field Name="SumofCommissionEee1" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Currency})"><FormattedValue>10.69</FormattedValue><Value>10.69</Value></Field>
</Section>
</GroupFooter>
</Group>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="LocationGroupFooterText1" FieldName="{@LocationGroupFooterText}"><FormattedValue>Total Katar pipin (tran: 9)</FormattedValue><Value>Total Katar pipin (tran: 9)</Value></Field>
<Field Name="SumofCommissionBbb2" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Location})"><FormattedValue>9.10</FormattedValue><Value>9.10</Value></Field>
<Field Name="SumofCommissionEee2" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Location})"><FormattedValue>2.01</FormattedValue><Value>2.01</Value></Field>
</Section>
</GroupFooter>
</Group>
<Group Level="1">
<GroupHeader>
<Section SectionNumber="0">
<Field Name="GroupNameLocation1" FieldName="GroupName ({SubAgentSettlement.Location})"><FormattedValue>Samsung</FormattedValue><Value>Samsung</Value></Field>
</Section>
</GroupHeader>
<Group Level="2">
<GroupHeader>
<Section SectionNumber="0">
</Section>
</GroupHeader>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="GroupFooterText1" FieldName="{@GroupFooterText}"><FormattedValue>Total BBB (tran: 5)</FormattedValue><Value>Total BBB (tran: 5)</Value></Field>
<Field Name="SumofCommission1" FieldName="Sum ({SubAgentSettlement.Commission}, {SubAgentSettlement.Currency})"><FormattedValue>16.00</FormattedValue><Value>16.00</Value></Field>
<Field Name="SumofCommissionBbb1" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Currency})"><FormattedValue>17.00</FormattedValue><Value>17.00</Value></Field>
<Field Name="SumofCommissionEee1" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Currency})"><FormattedValue>17.46</FormattedValue><Value>17.46</Value></Field>
</Section>
</GroupFooter>
</Group>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="LocationGroupFooterText1" FieldName="{@LocationGroupFooterText}"><FormattedValue>Total Samsung (tran: 15)</FormattedValue><Value>Total Samsung (tran: 5)</Value></Field>
<Field Name="SumofCommissionBbb2" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Location})"><FormattedValue>5.00</FormattedValue><Value>5.00</Value></Field>
<Field Name="SumofCommissionEee2" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Location})"><FormattedValue>17.46</FormattedValue><Value>17.46</Value></Field>
</Section>
</GroupFooter>
</Group>
<Group Level="1">
<GroupHeader>
<Section SectionNumber="0">
<Field Name="GroupNameLocation1" FieldName="GroupName ({SubAgentSettlement.Location})"><FormattedValue>Erica</FormattedValue><Value>Erica</Value></Field>
</Section>
</GroupHeader>
<Group Level="2">
<GroupHeader>
<Section SectionNumber="0">
</Section>
</GroupHeader>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="GroupFooterText1" FieldName="{@GroupFooterText}"><FormattedValue>Total BBB (tran: 5)</FormattedValue><Value>Total BBB (tran: 5)</Value></Field>
<Field Name="SumofCommission1" FieldName="Sum ({SubAgentSettlement.Commission}, {SubAgentSettlement.Currency})"><FormattedValue>6.10</FormattedValue><Value>6.10</Value></Field>
<Field Name="SumofCommissionBbb1" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Currency})"><FormattedValue>6.12</FormattedValue><Value>6.12</Value></Field>
<Field Name="SumofCommissionEee1" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Currency})"><FormattedValue>88.08</FormattedValue><Value>88.08</Value></Field>
</Section>
</GroupFooter>
</Group>
<Group Level="2">
<GroupHeader>
<Section SectionNumber="0">
</Section>
</GroupHeader>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="GroupFooterText1" FieldName="{@GroupFooterText}"><FormattedValue>Total EEE (tran: 2)</FormattedValue><Value>Total EEE (tran: 2)</Value></Field>
<Field Name="SumofCommission1" FieldName="Sum ({SubAgentSettlement.Commission}, {SubAgentSettlement.Currency})"><FormattedValue>13.00</FormattedValue><Value>13.00</Value></Field>
<Field Name="SumofCommissionBbb1" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Currency})"><FormattedValue>15.87</FormattedValue><Value>15.87</Value></Field>
<Field Name="SumofCommissionEee1" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Currency})"><FormattedValue>13.00</FormattedValue><Value>13.00</Value></Field>
</Section>
</GroupFooter>
</Group>
<Group Level="2">
<GroupHeader>
<Section SectionNumber="0">
</Section>
</GroupHeader>
<Details Level="3">
<Section SectionNumber="0">
</Section>
</Details>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="GroupFooterText1" FieldName="{@GroupFooterText}"><FormattedValue>Total UUU (transactions: 1)</FormattedValue><Value>Total UUU (transactions: 1)</Value></Field>
<Field Name="SumofCommission1" FieldName="Sum ({SubAgentSettlement.Commission}, {SubAgentSettlement.Currency})"><FormattedValue>22.00</FormattedValue><Value>22.00</Value></Field>
<Field Name="SumofCommissionBbb1" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Currency})"><FormattedValue>32.70</FormattedValue><Value>32.70</Value></Field>
<Field Name="SumofCommissionEee1" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Currency})"><FormattedValue>41.38</FormattedValue><Value>41.38</Value></Field>
</Section>
</GroupFooter>
</Group>
<GroupFooter>
<Section SectionNumber="0">
<Field Name="LocationGroupFooterText1" FieldName="{@LocationGroupFooterText}"><FormattedValue>Total Erica (tran: 8)</FormattedValue><Value>Total Erica (tran: 8)</Value></Field>
<Field Name="SumofCommissionBbb2" FieldName="Sum ({SubAgentSettlement.CommissionBBB}, {SubAgentSettlement.Location})"><FormattedValue>4.87</FormattedValue><Value>4.87</Value></Field>
<Field Name="SumofCommissionEee2" FieldName="Sum ({SubAgentSettlement.CommissionEEE}, {SubAgentSettlement.Location})"><FormattedValue>2.66</FormattedValue><Value>2.66</Value></Field>
</Section>
</GroupFooter>
</Group>
<ReportFooter>
<Section SectionNumber="0">
<Text Name="Text11"><TextValue>Total num of tran:</TextValue>
</Text>
<Field Name="CountofDate1" FieldName="Count ({SubAgentSettlement.Date})"><FormattedValue>126</FormattedValue><Value>126</Value></Field>
<Field Name="CurrencyRateLine1" FieldName="{@CurrencyRateLine}"><FormattedValue>Curr on date: 12/03/2009</FormattedValue><Value>Curr on date: 12/03/2009</Value></Field>
<Subreport Name="Subreport1">
<ReportHeader>
</ReportHeader>
<Details Level="1">
<Section SectionNumber="0">
<Field Name="CurrencyLine1" FieldName="{@CurrencyLine}"><FormattedValue>1 EEE = 10.12345 UUU</FormattedValue><Value>1 EEE = 10.12345 UUU</Value></Field>
</Section>
</Details>
<Details Level="1">
<Section SectionNumber="0">
<Field Name="CurrencyLine1" FieldName="{@CurrencyLine}"><FormattedValue>1 EEE = 7.12345 BBB</FormattedValue><Value>1 EEE = 7.12345 BBB</Value></Field>
</Section>
</Details>
<Details Level="1">
<Section SectionNumber="0">
<Field Name="CurrencyLine1" FieldName="{@CurrencyLine}"><FormattedValue>1 UUU = 6.12345 BBB</FormattedValue><Value>1 UUU = 6.123456 BBB</Value></Field>
</Section>
</Details>
<ReportFooter>
<Section SectionNumber="0">
</Section>
</ReportFooter>
</Subreport>
</Section>
<Section SectionNumber="1">
</Section>
</ReportFooter>
</CrystalReport>

The easiest thing to do in Oracle is use an XMLTYPE which allows you to access your XML string using XPATH expressions. I will give a small example, but you should be able to adapt it for your own XML:

declare
  v_xmlstring clob := '<myxml><field1>value1</field1></myxml>';
  v_xml xmltype;
begin
  v_xml := xmltype.createxml(v_xmlstring);
  dbms_output.put_line(v_xml.extract('/myxml/field1/text()').getstringval());
end;
/

This will print value1.

Given a string or a clob, you can create an XMLTYPE fron it using the xmltype.createxml() method.

Then your task becomes extracting the various values out of the XML. You do that with XPATH expressions, but it's a two stage process. When you call extract() on an XMLType it returns another XMLType - to get the text value of it, you call getstringval() on the XMLType, which is what I did below.

Now you have a further problem, in that your XML document has a repeating section, and you want to get a set of values from each section - you do this by looping over xml - something like the following code would work (remember xpath indexes start at 1, not zero like most arrays!):

declare
  v_xmlstring clob := '<myxml>
                         <field1>value1</field1>
                         <field1>value2</field1>
                         <field1>value3</field1>
                       </myxml>';
  v_xml xmltype;
  v_count integer := 1;
begin
  v_xml := xmltype.createxml(v_xmlstring);
  while v_xml.existsNode('/myxml/field1['||v_count||']') = 1 loop
    dbms_output.put_line(v_xml.extract('/myxml/field1['||v_count||']/text()').getStringVal());
    v_count := v_count + 1;
  end loop;
end;

Take a look at this..

You may need to cast your clob to XMLType before passing to the proc using Sys.XMLType.createXML()

继续阅读：oracle plsql sql

Read data from XML file

更多精彩内容

精彩评论

最新问答

央视是哪个频道？

请问买过的朋友，舒提啦旅行箱实际使用体验如何？？

检查不孕不育需要的费用？

海信ULED电视画质有什么不同的地方?？

钉子可以挂的住画框幕布吗？

问答排行榜

王昌瑞《潜梦追凶》剧组庆生新锐演员未来可期？

Is it allowed to ask users to enter credit card details for own payment method?

Escaping "<" in Perl-generated XML

imessage会显示已读吗？

微信重新建群怎么建？