?
Solved

tsql to process an xml and replace the values

Posted on 2011-10-14
13
Medium Priority
?
272 Views
Last Modified: 2012-05-12
this is file............
<Root>
  <TOInvoiceExtractBatch DocGenRunId="736" RunDt="9/8/2011 5:31:43 PM">
    <DocGenRunId>736</DocGenRunId>
    <RunDt>2011-09-08T05:31:43</RunDt>
    <BatchInvoiceCount>4</BatchInvoiceCount>
    <BatchTotalAmt>3292.64</BatchTotalAmt>
    <InvoiceHeaders List="Ax.Frameworks.ValueObjects.TransientObjects.TOInvoiceExtractHeader">
      <TOInvoiceExtractHeader InvoiceNo="500000634" InvoiceId="636">
        <InvoiceNo>500000634</InvoiceNo>
        <InvoiceId>636</InvoiceId>
        <InvoiceRunDate>2011-09-08T12:00:00</InvoiceRunDate>
        <InvoiceStartDate>2011-07-01T12:00:00</InvoiceStartDate>
        <InvoiceEndDate>2011-08-01T12:00:00</InvoiceEndDate>
        <InvoiceBillingPeriod>7/1/2011 - 8/1/2011</InvoiceBillingPeriod>
        <BusUnitName>xxxxxxxxxxxxxxx</BusUnitName>
        <BusUnitCode>MSC</BusUnitCode>
        <BusUnitId>4</BusUnitId>
        <BusUnitInvoiceName>xxxxxxxxxxxxxxx</BusUnitInvoiceName>
        <BusUnitTradeAs />
        <BusUnitRef>103</BusUnitRef>
        <BusUnitBusRef />
        <BusUnitAltRef />
        <BusUnitTaxCode />
        <BusUnitAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street>xxxxxxxxxxxxxxx Swedesford Road</Street>
          <Suburb />
          <City>Berwyn</City>
          <StateProvince>PA</StateProvince>
          <County>Chester</County>
          <ZipCode>19312</ZipCode>
          <CountryRegion>United States</CountryRegion>
          <TaxArea>390296350</TaxArea>
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString>xxxxxxxxxxxxxxx Swedesford Road,
Berwyn,
Chester,
PA,
19312,
United States</AddressAsFormattedString>
        </BusUnitAddress>
        <BusUnitBranch>None</BusUnitBranch>
        <PartyInvoiceName>Desert Sky Middle School</PartyInvoiceName>
        <PartyBusEmail />
        <PartyHomeEmail />
        <PartyNo>500035</PartyNo>
        <PartyId>89541</PartyId>
        <PartyAccountNo>500035-0001</PartyAccountNo>
        <PartyRef>001Q000000OwssPIAR</PartyRef>
        <PartyBusRef />
        <PartyAltRef />
        <PartyTaxCode />
        <PartyIsElectronicInvoice>false</PartyIsElectronicInvoice>
        <PartyIsNew>false</PartyIsNew>
        <PartyLanguage>French</PartyLanguage>
        <PartyCurrentAccountBalance>0.00</PartyCurrentAccountBalance>
        <PartyPrevInvoiceTotal>0.00</PartyPrevInvoiceTotal>
        <PartyBranch>None</PartyBranch>
        <PartyContactName>George Wallace</PartyContactName>
        <InvoiceDetailCount>1</InvoiceDetailCount>
        <InvoiceTotalAmount>18.00</InvoiceTotalAmount>
        <InvoiceTotalAmtTax>1.64</InvoiceTotalAmtTax>
        <InvoiceTotalGrossAmount>19.64</InvoiceTotalGrossAmount>
        <IsConsolidated>false</IsConsolidated>
        <ConsolidateId>0</ConsolidateId>
        <ConsolidationName>None</ConsolidationName>
        <InvoiceContext>Periodic</InvoiceContext>
        <BillingAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street />
          <Suburb />
          <City />
          <StateProvince />
          <County />
          <ZipCode />
          <CountryRegion />
          <TaxArea />
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString />
        </BillingAddress>
        <LocationAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street>98950 Rankin Loop</Street>
          <Suburb />
          <City>Tucson</City>
          <StateProvince>AZ</StateProvince>
          <County />
          <ZipCode>85754</ZipCode>
          <CountryRegion>United States</CountryRegion>
          <TaxArea>030190310</TaxArea>
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString>98950 Rankin Loop,
Tucson,
AZ,
85754,
United States</AddressAsFormattedString>
        </LocationAddress>
        <InvoiceDetails List="Ax.Frameworks.ValueObjects.TransientObjects.TOInvoiceExtractDetail">
          <TOInvoiceExtractDetail ContractId="501018" FlowId="294063" AssetHdrId="136021">
            <FlowId>294063</FlowId>
            <AssetHdrId>136021</AssetHdrId>
            <ContractId>501018</ContractId>
            <FlowType>Delivery-Fuel Surcharge</FlowType>
            <Purpose>Fee</Purpose>
            <CustomFlowReference />
            <CustomFlowExtReference />
            <CustomFlowQuickNote />
            <ContractOperatingUnit>AZ-Tucson</ContractOperatingUnit>
            <ContractInInertia>false</ContractInInertia>
            <ContractReference />
            <Product>Operating  Lease</Product>
            <Program />
            <AssetNumItems>1</AssetNumItems>
            <AssetType>Classroom Double - 12 x 56</AssetType>
            <AssetName>101198</AssetName>
            <AssetModel />
            <AssetDescription />
            <AssetReference>101198CLSMDBL</AssetReference>
            <AssetSerial>CAVAZD881097X</AssetSerial>
            <AssetColour />
            <AssetBaseLocation type="TOInvoiceExtractAddress" AddressId="">
              <Street>3480 S. BROADMONT DRIVE</Street>
              <Suburb />
              <City>Tucson</City>
              <StateProvince>AZ</StateProvince>
              <County />
              <ZipCode>85713</ZipCode>
              <CountryRegion>United States</CountryRegion>
              <TaxArea>030190310</TaxArea>
              <OverrideTaxArea>false</OverrideTaxArea>
              <AddressAsFormattedString>3480 S. BROADMONT DRIVE,
Tucson,
AZ,
85713,
United States</AddressAsFormattedString>
            </AssetBaseLocation>
            <AssetCurrentLocation type="TOInvoiceExtractAddress" AddressId="">
              <Street>98950 Rankin L</Street>
              <Suburb />
              <City />
              <StateProvince />
              <County />
              <ZipCode>85754</ZipCode>
              <CountryRegion />
              <TaxArea>030190310</TaxArea>
              <OverrideTaxArea>false</OverrideTaxArea>
              <AddressAsFormattedString>98950 Rankin L,
85754</AddressAsFormattedString>
            </AssetCurrentLocation>
            <AssetSubLocation />
            <PreviousPayments>0.00</PreviousPayments>
            <PrevInvoiceTotal>0.00</PrevInvoiceTotal>
            <OutstandingBalance>0.00</OutstandingBalance>
            <AmtPrincipal>0.00</AmtPrincipal>
            <AmtInterest>0.00</AmtInterest>
            <AmtRental>0.00</AmtRental>
            <AmtGrossNetted>19.64</AmtGrossNetted>
            <AmtTotalTax>1.64</AmtTotalTax>
            <AmtTotal>18.00</AmtTotal>
            <NettNo>0</NettNo>
            <InstallmentNo>0</InstallmentNo>
            <CalcDate>2011-07-01T12:00:00</CalcDate>
            <ExpectedDate>2011-07-01T12:00:00</ExpectedDate>
            <TaxPointDate>0001-01-01T12:00:00</TaxPointDate>
            <Custom1 type="TOCustomValue" Name="Project #" Value="897">
              <Name>Project #</Name>
              <Value>897</Value>
            </Custom1>
            <Custom2 type="TOCustomValue" Name="Customer PO #" Value="965">
              <Name>Customer PO #</Name>
              <Value>965</Value>
            </Custom2>
          </TOInvoiceExtractDetail>
        </InvoiceDetails>
      </TOInvoiceExtractHeader>
      <TOInvoiceExtractHeader InvoiceNo="500000635" InvoiceId="637">
        <InvoiceNo>500000635</InvoiceNo>
        <InvoiceId>637</InvoiceId>
        <InvoiceRunDate>2011-09-08T12:00:00</InvoiceRunDate>
        <InvoiceStartDate>2011-07-01T12:00:00</InvoiceStartDate>
        <InvoiceEndDate>2011-08-01T12:00:00</InvoiceEndDate>
        <InvoiceBillingPeriod>7/1/2011 - 8/1/2011</InvoiceBillingPeriod>
        <BusUnitName>xxxxxxxxxxxxxxx</BusUnitName>
        <BusUnitCode>MSC</BusUnitCode>
        <BusUnitId>4</BusUnitId>
        <BusUnitInvoiceName>xxxxxxxxxxxxxxx</BusUnitInvoiceName>
        <BusUnitTradeAs />
        <BusUnitRef>103</BusUnitRef>
        <BusUnitBusRef />
        <BusUnitAltRef />
        <BusUnitTaxCode />
        <BusUnitAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street>xxxxxxxxxxxxxxx Swedesford Road</Street>
          <Suburb />
          <City>Berwyn</City>
          <StateProvince>PA</StateProvince>
          <County>Chester</County>
          <ZipCode>19312</ZipCode>
          <CountryRegion>United States</CountryRegion>
          <TaxArea>390296350</TaxArea>
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString>xxxxxxxxxxxxxxx Swedesford Road,
Berwyn,
Chester,
PA,
19312,
United States</AddressAsFormattedString>
        </BusUnitAddress>
        <BusUnitBranch>None</BusUnitBranch>
        <PartyInvoiceName>Desert Sky Middle School</PartyInvoiceName>
        <PartyBusEmail />
        <PartyHomeEmail />
        <PartyNo>500037</PartyNo>
        <PartyId>89543</PartyId>
        <PartyAccountNo>500037-0001</PartyAccountNo>
        <PartyRef>a0MQ0000002mN2EMAU</PartyRef>
        <PartyBusRef />
        <PartyAltRef />
        <PartyTaxCode />
        <PartyIsElectronicInvoice>false</PartyIsElectronicInvoice>
        <PartyIsNew>false</PartyIsNew>
        <PartyLanguage>French</PartyLanguage>
        <PartyCurrentAccountBalance>0.00</PartyCurrentAccountBalance>
        <PartyPrevInvoiceTotal>0.00</PartyPrevInvoiceTotal>
        <PartyBranch>None</PartyBranch>
        <PartyContactName>Account Payables</PartyContactName>
        <InvoiceDetailCount>1</InvoiceDetailCount>
        <InvoiceTotalAmount>1000.00</InvoiceTotalAmount>
        <InvoiceTotalAmtTax>91.00</InvoiceTotalAmtTax>
        <InvoiceTotalGrossAmount>1091.00</InvoiceTotalGrossAmount>
        <IsConsolidated>false</IsConsolidated>
        <ConsolidateId>0</ConsolidateId>
        <ConsolidationName>None</ConsolidationName>
        <InvoiceContext>Periodic</InvoiceContext>
        <BillingAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street>98950 Rankin Loop</Street>
          <Suburb />
          <City>Tucson</City>
          <StateProvince>AZ</StateProvince>
          <County />
          <ZipCode>85754</ZipCode>
          <CountryRegion>United States</CountryRegion>
          <TaxArea>030190310</TaxArea>
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString>98950 Rankin Loop,
Tucson,
AZ,
85754,
United States</AddressAsFormattedString>
        </BillingAddress>
        <LocationAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street />
          <Suburb />
          <City />
          <StateProvince />
          <County />
          <ZipCode />
          <CountryRegion />
          <TaxArea />
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString />
        </LocationAddress>
        <InvoiceDetails List="Ax.Frameworks.ValueObjects.TransientObjects.TOInvoiceExtractDetail">
          <TOInvoiceExtractDetail ContractId="501018" FlowId="294077" AssetHdrId="136021">
            <FlowId>294077</FlowId>
            <AssetHdrId>136021</AssetHdrId>
            <ContractId>501018</ContractId>
            <FlowType>Installment</FlowType>
            <Purpose>Installment</Purpose>
            <CustomFlowReference />
            <CustomFlowExtReference />
            <CustomFlowQuickNote />
            <ContractOperatingUnit>AZ-Tucson</ContractOperatingUnit>
            <ContractInInertia>false</ContractInInertia>
            <ContractReference />
            <Product>Operating  Lease</Product>
            <Program />
            <AssetNumItems>1</AssetNumItems>
            <AssetType>Classroom Double - 12 x 56</AssetType>
            <AssetName>101198</AssetName>
            <AssetModel />
            <AssetDescription />
            <AssetReference>101198CLSMDBL</AssetReference>
            <AssetSerial>CAVAZD881097X</AssetSerial>
            <AssetColour />
            <AssetBaseLocation type="TOInvoiceExtractAddress" AddressId="">
              <Street>3480 S. BROADMONT DRIVE</Street>
              <Suburb />
              <City>Tucson</City>
              <StateProvince>AZ</StateProvince>
              <County />
              <ZipCode>85713</ZipCode>
              <CountryRegion>United States</CountryRegion>
              <TaxArea>030190310</TaxArea>
              <OverrideTaxArea>false</OverrideTaxArea>
              <AddressAsFormattedString>3480 S. BROADMONT DRIVE,
Tucson,
AZ,
85713,
United States</AddressAsFormattedString>
            </AssetBaseLocation>
            <AssetCurrentLocation type="TOInvoiceExtractAddress" AddressId="">
              <Street>98950 Rankin L</Street>
              <Suburb />
              <City />
              <StateProvince />
              <County />
              <ZipCode>85754</ZipCode>
              <CountryRegion />
              <TaxArea>030190310</TaxArea>
              <OverrideTaxArea>false</OverrideTaxArea>
              <AddressAsFormattedString>98950 Rankin L,
85754</AddressAsFormattedString>
            </AssetCurrentLocation>
            <AssetSubLocation />
            <PreviousPayments>0.00</PreviousPayments>
            <PrevInvoiceTotal>19.64</PrevInvoiceTotal>
            <OutstandingBalance>19.64</OutstandingBalance>
            <AmtPrincipal>0.00</AmtPrincipal>
            <AmtInterest>0.00</AmtInterest>
            <AmtRental>0.00</AmtRental>
            <AmtGrossNetted>1091.00</AmtGrossNetted>
            <AmtTotalTax>91.00</AmtTotalTax>
            <AmtTotal>1000.00</AmtTotal>
            <NettNo>0</NettNo>
            <InstallmentNo>1</InstallmentNo>
            <CalcDate>2011-07-01T12:00:00</CalcDate>
            <ExpectedDate>2011-07-01T12:00:00</ExpectedDate>
            <TaxPointDate>0001-01-01T12:00:00</TaxPointDate>
            <TaxDetails List="Ax.Frameworks.ValueObjects.TransientObjects.TOInvoiceExtractDetailTax">
              <TOInvoiceExtractDetailTax TaxTypeId="4" ExternalName="City Tax">
                <TaxTypeId>4</TaxTypeId>
                <Name>City Tax</Name>
                <Code>CITY</Code>
                <Reference>CITY</Reference>
                <ExternalName>City Tax</ExternalName>
                <AmtTax>20.00</AmtTax>
              </TOInvoiceExtractDetailTax>
              <TOInvoiceExtractDetailTax TaxTypeId="3" ExternalName="State Tax">
                <TaxTypeId>3</TaxTypeId>
                <Name>State Tax</Name>
                <Code>STATE</Code>
                <Reference>STATE</Reference>
                <ExternalName>State Tax</ExternalName>
                <AmtTax>66.00</AmtTax>
              </TOInvoiceExtractDetailTax>
              <TOInvoiceExtractDetailTax TaxTypeId="5" ExternalName="County Tax">
                <TaxTypeId>5</TaxTypeId>
                <Name>County Tax</Name>
                <Code>COUNTY</Code>
                <Reference>COUNTY</Reference>
                <ExternalName>County Tax</ExternalName>
                <AmtTax>5.00</AmtTax>
              </TOInvoiceExtractDetailTax>
            </TaxDetails>
            <Custom1 type="TOCustomValue" Name="Project #" Value="897">
              <Name>Project #</Name>
              <Value>897</Value>
            </Custom1>
            <Custom2 type="TOCustomValue" Name="Customer PO #" Value="965">
              <Name>Customer PO #</Name>
              <Value>965</Value>
            </Custom2>
          </TOInvoiceExtractDetail>
        </InvoiceDetails>
      </TOInvoiceExtractHeader>
      <TOInvoiceExtractHeader InvoiceNo="500000636" InvoiceId="638">
        <InvoiceNo>500000636</InvoiceNo>
        <InvoiceId>638</InvoiceId>
        <InvoiceRunDate>2011-09-08T12:00:00</InvoiceRunDate>
        <InvoiceStartDate>2011-08-01T12:00:00</InvoiceStartDate>
        <InvoiceEndDate>2011-09-01T12:00:00</InvoiceEndDate>
        <InvoiceBillingPeriod>8/1/2011 - 9/1/2011</InvoiceBillingPeriod>
        <BusUnitName>xxxxxxxxxxxxxxx</BusUnitName>
        <BusUnitCode>MSC</BusUnitCode>
        <BusUnitId>4</BusUnitId>
        <BusUnitInvoiceName>xxxxxxxxxxxxxxx</BusUnitInvoiceName>
        <BusUnitTradeAs />
        <BusUnitRef>103</BusUnitRef>
        <BusUnitBusRef />
        <BusUnitAltRef />
        <BusUnitTaxCode />
        <BusUnitAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street>xxxxxxxxxxxxxxx Swedesford Road</Street>
          <Suburb />
          <City>Berwyn</City>
          <StateProvince>PA</StateProvince>
          <County>Chester</County>
          <ZipCode>19312</ZipCode>
          <CountryRegion>United States</CountryRegion>
          <TaxArea>390296350</TaxArea>
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString>xxxxxxxxxxxxxxx Swedesford Road,
Berwyn,
Chester,
PA,
19312,
United States</AddressAsFormattedString>
        </BusUnitAddress>
        <BusUnitBranch>None</BusUnitBranch>
        <PartyInvoiceName>Desert Sky Middle School</PartyInvoiceName>
        <PartyBusEmail />
        <PartyHomeEmail />
        <PartyNo>500037</PartyNo>
        <PartyId>89543</PartyId>
        <PartyAccountNo>500037-0001</PartyAccountNo>
        <PartyRef>a0MQ0000002mN2EMAU</PartyRef>
        <PartyBusRef />
        <PartyAltRef />
        <PartyTaxCode />
        <PartyIsElectronicInvoice>false</PartyIsElectronicInvoice>
        <PartyIsNew>false</PartyIsNew>
        <PartyLanguage>French</PartyLanguage>
        <PartyCurrentAccountBalance>0.00</PartyCurrentAccountBalance>
        <PartyPrevInvoiceTotal>0.00</PartyPrevInvoiceTotal>
        <PartyBranch>None</PartyBranch>
        <PartyContactName>Account Payables</PartyContactName>
        <InvoiceDetailCount>1</InvoiceDetailCount>
        <InvoiceTotalAmount>1000.00</InvoiceTotalAmount>
        <InvoiceTotalAmtTax>91.00</InvoiceTotalAmtTax>
        <InvoiceTotalGrossAmount>1091.00</InvoiceTotalGrossAmount>
        <IsConsolidated>false</IsConsolidated>
        <ConsolidateId>0</ConsolidateId>
        <ConsolidationName>None</ConsolidationName>
        <InvoiceContext>Periodic</InvoiceContext>
        <BillingAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street>98950 Rankin Loop</Street>
          <Suburb />
          <City>Tucson</City>
          <StateProvince>AZ</StateProvince>
          <County />
          <ZipCode>85754</ZipCode>
          <CountryRegion>United States</CountryRegion>
          <TaxArea>030190310</TaxArea>
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString>98950 Rankin Loop,
Tucson,
AZ,
85754,
United States</AddressAsFormattedString>
        </BillingAddress>
        <LocationAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street />
          <Suburb />
          <City />
          <StateProvince />
          <County />
          <ZipCode />
          <CountryRegion />
          <TaxArea />
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString />
        </LocationAddress>
        <InvoiceDetails List="Ax.Frameworks.ValueObjects.TransientObjects.TOInvoiceExtractDetail">
          <TOInvoiceExtractDetail ContractId="501018" FlowId="294065" AssetHdrId="136021">
            <FlowId>294065</FlowId>
            <AssetHdrId>136021</AssetHdrId>
            <ContractId>501018</ContractId>
            <FlowType>Installment</FlowType>
            <Purpose>Installment</Purpose>
            <CustomFlowReference />
            <CustomFlowExtReference />
            <CustomFlowQuickNote />
            <ContractOperatingUnit>AZ-Tucson</ContractOperatingUnit>
            <ContractInInertia>false</ContractInInertia>
            <ContractReference />
            <Product>Operating  Lease</Product>
            <Program />
            <AssetNumItems>1</AssetNumItems>
            <AssetType>Classroom Double - 12 x 56</AssetType>
            <AssetName>101198</AssetName>
            <AssetModel />
            <AssetDescription />
            <AssetReference>101198CLSMDBL</AssetReference>
            <AssetSerial>CAVAZD881097X</AssetSerial>
            <AssetColour />
            <AssetBaseLocation type="TOInvoiceExtractAddress" AddressId="">
              <Street>3480 S. BROADMONT DRIVE</Street>
              <Suburb />
              <City>Tucson</City>
              <StateProvince>AZ</StateProvince>
              <County />
              <ZipCode>85713</ZipCode>
              <CountryRegion>United States</CountryRegion>
              <TaxArea>030190310</TaxArea>
              <OverrideTaxArea>false</OverrideTaxArea>
              <AddressAsFormattedString>3480 S. BROADMONT DRIVE,
Tucson,
AZ,
85713,
United States</AddressAsFormattedString>
            </AssetBaseLocation>
            <AssetCurrentLocation type="TOInvoiceExtractAddress" AddressId="">
              <Street>98950 Rankin L</Street>
              <Suburb />
              <City />
              <StateProvince />
              <County />
              <ZipCode>85754</ZipCode>
              <CountryRegion />
              <TaxArea>030190310</TaxArea>
              <OverrideTaxArea>false</OverrideTaxArea>
              <AddressAsFormattedString>98950 Rankin L,
85754</AddressAsFormattedString>
            </AssetCurrentLocation>
            <AssetSubLocation />
            <PreviousPayments>0.00</PreviousPayments>
            <PrevInvoiceTotal>1110.64</PrevInvoiceTotal>
            <OutstandingBalance>1110.64</OutstandingBalance>
            <AmtPrincipal>0.00</AmtPrincipal>
            <AmtInterest>0.00</AmtInterest>
            <AmtRental>0.00</AmtRental>
            <AmtGrossNetted>1091.00</AmtGrossNetted>
            <AmtTotalTax>91.00</AmtTotalTax>
            <AmtTotal>1000.00</AmtTotal>
            <NettNo>0</NettNo>
            <InstallmentNo>2</InstallmentNo>
            <CalcDate>2011-08-01T12:00:00</CalcDate>
            <ExpectedDate>2011-08-01T12:00:00</ExpectedDate>
            <TaxPointDate>0001-01-01T12:00:00</TaxPointDate>
            <TaxDetails List="Ax.Frameworks.ValueObjects.TransientObjects.TOInvoiceExtractDetailTax">
              <TOInvoiceExtractDetailTax TaxTypeId="4" ExternalName="City Tax">
                <TaxTypeId>4</TaxTypeId>
                <Name>City Tax</Name>
                <Code>CITY</Code>
                <Reference>CITY</Reference>
                <ExternalName>City Tax</ExternalName>
                <AmtTax>20.00</AmtTax>
              </TOInvoiceExtractDetailTax>
              <TOInvoiceExtractDetailTax TaxTypeId="3" ExternalName="State Tax">
                <TaxTypeId>3</TaxTypeId>
                <Name>State Tax</Name>
                <Code>STATE</Code>
                <Reference>STATE</Reference>
                <ExternalName>State Tax</ExternalName>
                <AmtTax>66.00</AmtTax>
              </TOInvoiceExtractDetailTax>
              <TOInvoiceExtractDetailTax TaxTypeId="5" ExternalName="County Tax">
                <TaxTypeId>5</TaxTypeId>
                <Name>County Tax</Name>
                <Code>COUNTY</Code>
                <Reference>COUNTY</Reference>
                <ExternalName>County Tax</ExternalName>
                <AmtTax>5.00</AmtTax>
              </TOInvoiceExtractDetailTax>
            </TaxDetails>
            <Custom1 type="TOCustomValue" Name="Project #" Value="897">
              <Name>Project #</Name>
              <Value>897</Value>
            </Custom1>
            <Custom2 type="TOCustomValue" Name="Customer PO #" Value="965">
              <Name>Customer PO #</Name>
              <Value>965</Value>
            </Custom2>
          </TOInvoiceExtractDetail>
        </InvoiceDetails>
      </TOInvoiceExtractHeader>
      <TOInvoiceExtractHeader InvoiceNo="500000637" InvoiceId="639">
        <InvoiceNo>500000637</InvoiceNo>
        <InvoiceId>639</InvoiceId>
        <InvoiceRunDate>2011-09-08T12:00:00</InvoiceRunDate>
        <InvoiceStartDate>2011-09-01T12:00:00</InvoiceStartDate>
        <InvoiceEndDate>2011-10-01T12:00:00</InvoiceEndDate>
        <InvoiceBillingPeriod>9/1/2011 - 10/1/2011</InvoiceBillingPeriod>
        <BusUnitName>xxxxxxxxxxxxxxx</BusUnitName>
        <BusUnitCode>MSC</BusUnitCode>
        <BusUnitId>4</BusUnitId>
        <BusUnitInvoiceName>xxxxxxxxxxxxxxx</BusUnitInvoiceName>
        <BusUnitTradeAs />
        <BusUnitRef>103</BusUnitRef>
        <BusUnitBusRef />
        <BusUnitAltRef />
        <BusUnitTaxCode />
        <BusUnitAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street>xxxxxxxxxxxxxxx Swedesford Road</Street>
          <Suburb />
          <City>Berwyn</City>
          <StateProvince>PA</StateProvince>
          <County>Chester</County>
          <ZipCode>19312</ZipCode>
          <CountryRegion>United States</CountryRegion>
          <TaxArea>390296350</TaxArea>
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString>xxxxxxxxxxxxxxx Swedesford Road,
Berwyn,
Chester,
PA,
19312,
United States</AddressAsFormattedString>
        </BusUnitAddress>
        <BusUnitBranch>None</BusUnitBranch>
        <PartyInvoiceName>Desert Sky Middle School</PartyInvoiceName>
        <PartyBusEmail />
        <PartyHomeEmail />
        <PartyNo>500037</PartyNo>
        <PartyId>89543</PartyId>
        <PartyAccountNo>500037-0001</PartyAccountNo>
        <PartyRef>a0MQ0000002mN2EMAU</PartyRef>
        <PartyBusRef />
        <PartyAltRef />
        <PartyTaxCode />
        <PartyIsElectronicInvoice>false</PartyIsElectronicInvoice>
        <PartyIsNew>false</PartyIsNew>
        <PartyLanguage>French</PartyLanguage>
        <PartyCurrentAccountBalance>0.00</PartyCurrentAccountBalance>
        <PartyPrevInvoiceTotal>0.00</PartyPrevInvoiceTotal>
        <PartyBranch>None</PartyBranch>
        <PartyContactName>Account Payables</PartyContactName>
        <InvoiceDetailCount>1</InvoiceDetailCount>
        <InvoiceTotalAmount>1000.00</InvoiceTotalAmount>
        <InvoiceTotalAmtTax>91.00</InvoiceTotalAmtTax>
        <InvoiceTotalGrossAmount>1091.00</InvoiceTotalGrossAmount>
        <IsConsolidated>false</IsConsolidated>
        <ConsolidateId>0</ConsolidateId>
        <ConsolidationName>None</ConsolidationName>
        <InvoiceContext>Periodic</InvoiceContext>
        <BillingAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street>98950 Rankin Loop</Street>
          <Suburb />
          <City>Tucson</City>
          <StateProvince>AZ</StateProvince>
          <County />
          <ZipCode>85754</ZipCode>
          <CountryRegion>United States</CountryRegion>
          <TaxArea>030190310</TaxArea>
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString>98950 Rankin Loop,
Tucson,
AZ,
85754,
United States</AddressAsFormattedString>
        </BillingAddress>
        <LocationAddress type="TOInvoiceExtractAddress" AddressId="">
          <Street />
          <Suburb />
          <City />
          <StateProvince />
          <County />
          <ZipCode />
          <CountryRegion />
          <TaxArea />
          <OverrideTaxArea>false</OverrideTaxArea>
          <AddressAsFormattedString />
        </LocationAddress>
        <InvoiceDetails List="Ax.Frameworks.ValueObjects.TransientObjects.TOInvoiceExtractDetail">
          <TOInvoiceExtractDetail ContractId="501018" FlowId="294066" AssetHdrId="136021">
            <FlowId>294066</FlowId>
            <AssetHdrId>136021</AssetHdrId>
            <ContractId>501018</ContractId>
            <FlowType>Installment</FlowType>
            <Purpose>Installment</Purpose>
            <CustomFlowReference />
            <CustomFlowExtReference />
            <CustomFlowQuickNote />
            <ContractOperatingUnit>AZ-Tucson</ContractOperatingUnit>
            <ContractInInertia>false</ContractInInertia>
            <ContractReference />
            <Product>Operating  Lease</Product>
            <Program />
            <AssetNumItems>1</AssetNumItems>
            <AssetType>Classroom Double - 12 x 56</AssetType>
            <AssetName>101198</AssetName>
            <AssetModel />
            <AssetDescription />
            <AssetReference>101198CLSMDBL</AssetReference>
            <AssetSerial>CAVAZD881097X</AssetSerial>
            <AssetColour />
            <AssetBaseLocation type="TOInvoiceExtractAddress" AddressId="">
              <Street>3480 S. BROADMONT DRIVE</Street>
              <Suburb />
              <City>Tucson</City>
              <StateProvince>AZ</StateProvince>
              <County />
              <ZipCode>85713</ZipCode>
              <CountryRegion>United States</CountryRegion>
              <TaxArea>030190310</TaxArea>
              <OverrideTaxArea>false</OverrideTaxArea>
              <AddressAsFormattedString>3480 S. BROADMONT DRIVE,
Tucson,
AZ,
85713,
United States</AddressAsFormattedString>
            </AssetBaseLocation>
            <AssetCurrentLocation type="TOInvoiceExtractAddress" AddressId="">
              <Street>98950 Rankin L</Street>
              <Suburb />
              <City />
              <StateProvince />
              <County />
              <ZipCode>85754</ZipCode>
              <CountryRegion />
              <TaxArea>030190310</TaxArea>
              <OverrideTaxArea>false</OverrideTaxArea>
              <AddressAsFormattedString>98950 Rankin L,
85754</AddressAsFormattedString>
            </AssetCurrentLocation>
            <AssetSubLocation />
            <PreviousPayments>0.00</PreviousPayments>
            <PrevInvoiceTotal>2201.64</PrevInvoiceTotal>
            <OutstandingBalance>2201.64</OutstandingBalance>
            <AmtPrincipal>0.00</AmtPrincipal>
            <AmtInterest>0.00</AmtInterest>
            <AmtRental>0.00</AmtRental>
            <AmtGrossNetted>1091.00</AmtGrossNetted>
            <AmtTotalTax>91.00</AmtTotalTax>
            <AmtTotal>1000.00</AmtTotal>
            <NettNo>0</NettNo>
            <InstallmentNo>3</InstallmentNo>
            <CalcDate>2011-09-01T12:00:00</CalcDate>
            <ExpectedDate>2011-09-01T12:00:00</ExpectedDate>
            <TaxPointDate>0001-01-01T12:00:00</TaxPointDate>
            <TaxDetails List="Ax.Frameworks.ValueObjects.TransientObjects.TOInvoiceExtractDetailTax">
              <TOInvoiceExtractDetailTax TaxTypeId="4" ExternalName="City Tax">
                <TaxTypeId>4</TaxTypeId>
                <Name>City Tax</Name>
                <Code>CITY</Code>
                <Reference>CITY</Reference>
                <ExternalName>City Tax</ExternalName>
                <AmtTax>20.00</AmtTax>
              </TOInvoiceExtractDetailTax>
              <TOInvoiceExtractDetailTax TaxTypeId="3" ExternalName="State Tax">
                <TaxTypeId>3</TaxTypeId>
                <Name>State Tax</Name>
                <Code>STATE</Code>
                <Reference>STATE</Reference>
                <ExternalName>State Tax</ExternalName>
                <AmtTax>66.00</AmtTax>
              </TOInvoiceExtractDetailTax>
              <TOInvoiceExtractDetailTax TaxTypeId="5" ExternalName="County Tax">
                <TaxTypeId>5</TaxTypeId>
                <Name>County Tax</Name>
                <Code>COUNTY</Code>
                <Reference>COUNTY</Reference>
                <ExternalName>County Tax</ExternalName>
                <AmtTax>5.00</AmtTax>
              </TOInvoiceExtractDetailTax>
            </TaxDetails>
            <Custom1 type="TOCustomValue" Name="Project #" Value="897">
              <Name>Project #</Name>
              <Value>897</Value>
            </Custom1>
            <Custom2 type="TOCustomValue" Name="Customer PO #" Value="965">
              <Name>Customer PO #</Name>
              <Value>965</Value>
            </Custom2>
          </TOInvoiceExtractDetail>
        </InvoiceDetails>
      </TOInvoiceExtractHeader>
    </InvoiceHeaders>
  </TOInvoiceExtractBatch>
</Root>

Open in new window


i need to process all the flow type tags for a given french langauge along with BusUnitInvoiceName tag should be processed and replaced with database values for flow type  and also replace  BusUnitInvoiceName with hard coded value.

declare @content xml, @icontent int
declare @invoice int,@toString varchar (100), @value varchar (200), @flowtypevalue varchar (200),
@lang varchar (100),@flowtype varchar (200),@bcpcmd VARCHAR(2000),@FileName varchar(100),@xmlid NUMERIC(18,0)

SELECT  @FileName = 'C:\Temp\vkollati\InvoiceExtractResult.xml'
-- Load xml
SELECT @content = cast(BulkColumn as xml)
  FROM OPENROWSET(BULK N'C:\temp\vkollati\InvoiceExtract_2.xml', SINGLE_CLOB) as sample
  
exec sp_xml_preparedocument @icontent output, @content

declare csr cursor for 
	select invoice, lang, flowtype
	  from openxml(@icontent, '/Root/TOInvoiceExtractBatch/InvoiceHeaders/TOInvoiceExtractHeader', 1) 
		   with (invoice int './@InvoiceNo', 
			 lang varchar(100) './PartyLanguage',
			 flowtype varchar(100) './InvoiceDetails/TOInvoiceExtractDetail/FlowType')
	  where lang = 'French'
	  
open csr

-- Update content
fetch next from csr into @invoice, @lang, @flowtype
 while @@fetch_status = 0 begin
	set @value = 'Modular Space Corporation French'
	set @content.modify('replace value of (/Root/TOInvoiceExtractBatch/InvoiceHeaders/TOInvoiceExtractHeader[@InvoiceNo=sql:variable("@invoice")]/BusUnitInvoiceName/text())[1] with sql:variable("@value")')
	IF EXISTS( select french_description from mod_french_custom_flows where custom_flow_name = @flowtype) 
	   set @flowtypevalue = (select french_description from mod_french_custom_flows where custom_flow_name = @flowtype);
	ELSE
		set @flowtypevalue = @flowtype;
	set @content.modify('replace value of (/Root/TOInvoiceExtractBatch/InvoiceHeaders/TOInvoiceExtractHeader[@InvoiceNo=sql:variable("@invoice")]/InvoiceDetails/TOInvoiceExtractDetail/FlowType/text())[1] with sql:variable("@flowtypevalue")')
	
	fetch next from csr into @invoice, @lang, @flowtype
end

close csr
deallocate csr
exec sp_xml_removedocument @icontent

-- Resulting xml
---select @content

--truncating the table before it inserts new rows

truncate table [42Interface].[dbo].[xmlfeeds];

INSERT INTO [42Interface].[dbo].[xmlfeeds](xml_data)
VALUES(@content);

Open in new window


Please let me know
0
Comment
Question by:ModSpace
  • 7
  • 6
13 Comments
 

Author Comment

by:ModSpace
ID: 36970826
The above sql is not replacing the flowtype tags with database values. This is failing.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36970893
ModSpace,

How close is the code you have above? i.e., which pieces are missing from this that you want? If I understand "BusUnitInvoiceName tag should be processed and replaced with database values for flow type and also replace  BusUnitInvoiceName" correctly, it sounds like you need to connect somehow to another table or set of tables JOIN'd by FlowType retrieved from original XML.

I do not see anything pulling from your database yet, so what does that structure look like. Is there sample data you are testing with that matches up to the sample XML file you have provided?

My instinct tells me the Cursor approach is not the way to go, but I have not dived in deep enough. My gut, though, is you likely need to shred the XML --- at least to break up each independent XML snippet containing a FlowType tag --- JOIN to your other table(s) and replace data, then recombine XML for output. I know you did not want any physical tables created, but I see you using an [xmlfeeds] table as I suggested in my article. If that is the case, you may be able to use table variables to do most of this and then store final XML in that table to reduce any complications of bcp output.

But since you are truncating that table every time, you may be able to use it for storage of temporary data also if this process is guaranteed to only be run by one individual at a time (i.e., no risk of another process truncating table in the middle of a conversion).

Anyway, before speculating too much, we probably should get through more details as mentioned earlier. I should be back online later and will take a look at your responses then and try to mock up test environment to match your sample data. It might help to answer questions in my head on whether Cursor is the correct long-term solution for you.

Kevin
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36970918
I was typing during your posting your comment. I see now where you are calling to table mod_french_custom_flows -- I was just missing that before. Okay, so this is the code not working:

IF EXISTS( select french_description from mod_french_custom_flows where custom_flow_name = @flowtype)
         set @flowtypevalue = (select french_description from mod_french_custom_flows where custom_flow_name = @flowtype);
      ELSE
            set @flowtypevalue = @flowtype;

Does the other code work? i.e., Is BusUnitInvoiceName set to 'Modular Space Corporation French' appropriately?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36971013
Just doing some quick testing without the mod_french_custom_flows table and it seems to work just fine; therefore, the issue appears to be in the lookup of original flowtype against your database table. Therefore, a sample of that data will be helpful.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36971064
This works for me:
declare @content xml, @icontent int
declare @invoice int,@toString varchar (100), @value varchar (200), @flowtypevalue varchar (200),
@lang varchar (100),@flowtype varchar (200),@bcpcmd VARCHAR(2000),@FileName varchar(100),@xmlid NUMERIC(18,0)

declare @mod_french_custom_flows table (custom_flow_name varchar(100), french_description varchar(100));
insert into @mod_french_custom_flows(custom_flow_name, french_description)
select 'Delivery-Fuel Surcharge', 'Livraison-Surcharge Carburant' union
select 'Installment', 'Versement';

SELECT  @FileName = 'C:\Temp\vkollati\InvoiceExtractResult.xml'
-- Load xml
SELECT @content = cast(BulkColumn as xml)
 FROM OPENROWSET(BULK N'C:\temp\vkollati\InvoiceExtract_2.xml', SINGLE_CLOB) as sample

exec sp_xml_preparedocument @icontent output, @content

declare csr cursor for 
	select invoice, lang, flowtype
	  from openxml(@icontent, '/Root/TOInvoiceExtractBatch/InvoiceHeaders/TOInvoiceExtractHeader', 1) 
		   with (invoice int './@InvoiceNo', 
			 lang varchar(100) './PartyLanguage',
			 flowtype varchar(100) './InvoiceDetails/TOInvoiceExtractDetail/FlowType')
	  where lang = 'French'

open csr

-- Update content
fetch next from csr into @invoice, @lang, @flowtype
 while @@fetch_status = 0 begin
	set @value = 'Modular Space Corporation French'
	set @content.modify('replace value of (/Root/TOInvoiceExtractBatch/InvoiceHeaders/TOInvoiceExtractHeader[@InvoiceNo=sql:variable("@invoice")]/BusUnitInvoiceName/text())[1] with sql:variable("@value")')
	SET @flowtypevalue = COALESCE((SELECT french_description FROM @mod_french_custom_flows WHERE custom_flow_name = @flowtype), @flowtype);
	set @content.modify('replace value of (/Root/TOInvoiceExtractBatch/InvoiceHeaders/TOInvoiceExtractHeader[@InvoiceNo=sql:variable("@invoice")]/InvoiceDetails/TOInvoiceExtractDetail/FlowType/text())[1] with sql:variable("@flowtypevalue")')
	
	fetch next from csr into @invoice, @lang, @flowtype
end

close csr
deallocate csr
exec sp_xml_removedocument @icontent 


-- Resulting xml
select @content

Open in new window

0
 

Author Comment

by:ModSpace
ID: 36971144
declare csr cursor for
      select invoice, lang, flowtype
        from openxml(@icontent, '/Root/TOInvoiceExtractBatch/InvoiceHeaders/TOInvoiceExtractHeader', 1)
               with (invoice int './@InvoiceNo',
                   lang varchar(100) './PartyLanguage',
                   flowtype varchar(100) './InvoiceDetails/TOInvoiceExtractDetail/FlowType')---- this is not working it's not finding the flowtype
        where lang = 'French'

open csr

0
 

Author Comment

by:ModSpace
ID: 36971150
I have attached the xml file in the smae post.
0
 

Author Comment

by:ModSpace
ID: 36971155
not exactly attached it pasted in the post.
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 2000 total points
ID: 36971226
Hmm. It worked for me. I ran the OPENXML piece without the Cursor and other code and I could see the FlowType values. That is how I derived what to use as sample data:

declare @mod_french_custom_flows table (custom_flow_name varchar(100), french_description varchar(100));
insert into @mod_french_custom_flows(custom_flow_name, french_description)
select 'Delivery-Fuel Surcharge', 'Livraison-Surcharge Carburant' union
select 'Installment', 'Versement';

In your sample XML, I got 3 rows of Installment and 1 of Delivery-Fuel Surcharge. I was asking for sample of the mod_french_custom_flows data as it appears that is where the issue is --- at least when I run my test. Given my poor French, I used rough translations to give me sample data and table @mod_french_custom_flows. When I used this code (figured I would avoid the IF statement in case it was getting tripped up in that process), it works fine:

SET @flowtypevalue = COALESCE((SELECT french_description FROM @mod_french_custom_flows WHERE custom_flow_name = @flowtype), @flowtype);

I doubt the issue is the IF EXISTS() as that looks fine, though I am always paranoid when IF/ELSE statements do not include the BEGIN/END tags that you may get the wrong code included in IF/ELSE (e.g., if the @content.modify() is falling under the ELSE which won't apply if custom_flow_name is found in the database). Therefore, I suspect the data in your mod_french_custom_flows is not matching either because of spaces or some other extraneous characters and so you are getting the ELSE of the original @flowtype.
0
 

Author Comment

by:ModSpace
ID: 36971243
Thanks for the update.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36971250
To backup my last thought, I put back the IF/ELSE and it still works.

IF EXISTS( select french_description from @mod_french_custom_flows where custom_flow_name = @flowtype)
         set @flowtypevalue = (select french_description from @mod_french_custom_flows where custom_flow_name = @flowtype);
      ELSE
            set @flowtypevalue = @flowtype;

Therefore, I would check the data and data type of custom_flow_name and post that here!
0
 

Accepted Solution

by:
ModSpace earned 0 total points
ID: 36972091
Thanks for the update. it's working with my same script.
0
 

Author Closing Comment

by:ModSpace
ID: 36991325
my same script worked.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question