SQL Server Performance

SQL TO XML

Discussion in 'SQL Server 2005 General DBA Questions' started by cobby1812, Dec 6, 2010.

  1. cobby1812 New Member

    Good morning,
    I have a sql statement (shown below) , the result set needs to go into an xml format (shown below as well). To be homnest I havent got a clue how to do this. The section up to Case Information is all single entry, after that i.e the Case Information, the data needs to be returned per transaction, so if there are 5 invoices it will create 5 different AnswerAlpha parts, I am struggling to see how this can happen and I hope someone can point me in the right direction.
    SELECT --Case Information--
    'New Case' as Description,
    Left(convert(varchar, getdate(), 126), 22) as CreationDateTime,
    'Mark Cobb' as Origin,
    'COBBMJ1' as LookupClientId,
    Cast(ClientMaster.ClientNumber as varchar) + '/' + Cast(DebtorMaster.DebtorNumber as Char) as MatterReference,
    Upper(Left(Ltrim(DebtorMaster.Name), 15)) as MatterSearch,
    DebtorMaster.Name AS MatterDescription,
    'FWJ' as LookupAccountingGroupCode,
    'LT' as LookupDepartmentCode,
    'DR' as LookupWorkTypeCode,
    /** Enter Partner ID**/ ' ' LookupPartnerId,
    /** Enter Fee Earner ID**/ ' ' LookupFeeEarnerId,
    2 as LookupMatterBillingTemplateRSN,
    Left(Convert(Varchar, getdate(), 126), 10) as MatterBillingIncludeFromDate,
    'no' as MatterFinalBill,
    ' ' as MatterWIPCreditLimit,
    ' ' as MatterDisbursementsCreditLimit,
    'S' as LookupMatterChargeBandCode,
    'LT' as LookupCostCentreCode,
    1003 as LookupNominalCode,
    /** Add If Required**/ ' ' as LookupNameRSN,
    /** Add if Required**/ ' ' as LookupNameRSN,
    /** Add If Required**/ ' ' as LookupOfficeBankId,
    /** Add If Required**/ ' ' as LookupClientBankId,
    /** Add If Required**/ ' ' as LookupDepositBankId,
    /** Add If Required**/ ' ' as LookupStakeholderBankId,
    'NDM' as LookupCaseModuleCode,
    'NDM' as LookupCaseTypeCode,
    /** Add As Required**/ ' ' as LookupEventCode,
    /** Add As Required**/ ' ' as LookupNameRSN,
    --Defendant Details --
    /** Add As Required**/ ' ' as Contact,
    DebtorMaster.Name as DefendantName,
    DebtorMaster.AddressLine1 as DefAdd1,
    DebtorMaster.AddressLine2 AS DefAdd2,
    DebtorMaster.AddressLine3 AS DefAdd3,
    DebtorMaster.AddressLine4 AS DefAdd4,
    DebtorAccountStatic.PhoneNumber as DefTele,
    ' 'as DefFaxs,
    ' ' as DefEmail,
    --Supplier Details --
    ClientMaster.Name as SuppName,
    ClientAccountStaticA.ClientAccountContact as SuppContact,
    ClientMaster.AddressLine1 as SuppAdd1,
    ClientMaster.AddressLine2 as SuppAdd2,
    ClientMaster.AddressLine3 as SuppAdd3,
    ClientMaster.AddressLine4 as SuppAdd4,
    ClientAccountStaticA.ContactTelephoneNumber as SuppTele,
    /** Add as required **/ ' ' as SuppBankName,
    ClientAccountStaticB.BankSortCode1 as SuppSortCode,
    ClientAccountStaticB.BankAccountNumber1 as SuppAcctNum,
    ' ' as SuppIBAN,
    ' ' as SuppRef,

    --Case Information --
    OpenItems.DocumentDate as InvoiceDate,
    OpenItems.DueDate as OverdueDate,
    OpenItems.DocumentNumber as InvoiceNumber,
    OpenItems.DocumentReference as InvoiceRef,
    DType.FullDesc as InvoiceDescription,
    OpenItems.DocumentBalance as Amount

    FROM OpenItems
    LEFT OUTER JOIN DebtorAccountStatic
    ON OpenItems.ClientAFCNumber = DebtorAccountStatic.ClientAFCNumber
    AND OpenItems.ClientNumber = DebtorAccountStatic.ClientNumber
    AND OpenItems.BusinessType = DebtorAccountStatic.BusinessType
    AND OpenItems.ClientCurrencyCode = DebtorAccountStatic.ClientCurrencyCode
    AND OpenItems.DebtorAFCNumber = DebtorAccountStatic.DebtorAFCNumber
    AND OpenItems.DebtornNumber = DebtorAccountStatic.DebtorNumber
    AND OpenItems.DebtorCurrencyCode = DebtorAccountStatic.DebtorCurrencyCode

    RIGHT OUTER JOIN DebtorMaster
    ON DebtorAccountStatic.DebtorAFCNumber = DebtorMaster.DebtorAFCNumber
    AND DebtorAccountStatic.DebtorNumber = DebtorMaster.DebtorNumber

    LEFT OUTER JOIN ClientAccountStaticA
    ON OpenItems.ClientAFCNumber = ClientAccountStaticA.ClientAFCNumber
    AND OpenItems.ClientNumber = ClientAccountStaticA.ClientNumber
    AND OpenItems.BusinessType = ClientAccountStaticA.BusinessType
    AND OpenItems.ClientCurrencyCode = ClientAccountStaticA.ClientCurrencyCode

    LEFT OUTER JOIN ClientAccountStaticB
    ON OpenItems.ClientAFCNumber = ClientAccountStaticB.ClientAFCNumber
    AND OpenItems.ClientNumber = ClientAccountStaticB.ClientNumber
    AND OpenItems.BusinessType = ClientAccountStaticB.BusinessType
    AND OpenItems.ClientCurrencyCode = ClientAccountStaticB.ClientCurrencyCode

    RIGHT OUTER JOIN ClientMaster
    ON ClientAccountStaticA.ClientAFCNumber = ClientMaster.ClientAFCNumber
    AND ClientAccountStaticA.ClientNumber = ClientMaster.ClientNumber
    LEFT OUTER JOIN DType
    ON DType.Tabcode = OpenItems.DocumentType

    where ClientMaster.ClientNumber = 12274
    and DebtorMaster.DebtorNumber = 113733
    for XML RAW ('GalaxyLegal'), XMLSCHEMA('http://www.civica.co.uk/GalaxyLegal GalaxyLegal-v5-3.xsd')

    <?xml version="1.0" encoding="UTF-8" ?>
    <GalaxyLegal xmlns="http://www.civica.co.uk/GalaxyLegal" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.civica.co.uk/GalaxyLegal GalaxyLegal-v5-3.xsd">
    <FileIdentifier>
    <Description>New Case</Description>
    <CreationDateTime>2010-11-15T12:30:39</CreationDateTime>
    <Origin>Mark Cobb</Origin>
    </FileIdentifier>
    <Case>
    <Matter>
    <LookupClientId>COBBMJ1</LookupClientId>
    <AutogenerateMatterId />
    <MatterReference>12345/1234567</MatterReference>
    <MatterSearch>MARKCOBB</MatterSearch>
    <MatterDescription>Mark Cobb Ltd</MatterDescription>
    <LookupAccountingGroupCode>FWJ</LookupAccountingGroupCode>
    <LookupDepartmentCode>LT</LookupDepartmentCode>
    <LookupWorkTypeCode>DR</LookupWorkTypeCode>
    <LookupPartnerId>AW</LookupPartnerId>
    <LookupFeeEarnerId>NS</LookupFeeEarnerId>
    <MatterUseAutoBilling>
    <LookupMatterBillingTemplateRSN>2</LookupMatterBillingTemplateRSN>
    <MatterBillingIncludeFromDate>2010-11-15</MatterBillingIncludeFromDate>
    <MatterFinalBill>no</MatterFinalBill>
    </MatterUseAutoBilling>
    <MatterWIPcreditLimit>1000.00</MatterWIPcreditLimit>
    <MatterDisbursementsCreditLimit>100.00</MatterDisbursementsCreditLimit>
    <MatterBillingMethod>
    <MatterTimeValuationMethod>
    <MatterTimeValuationByClient>
    <LookupMatterChargeBandCode>S</LookupMatterChargeBandCode>
    </MatterTimeValuationByClient>
    </MatterTimeValuationMethod>
    </MatterBillingMethod>
    <MatterRevenueCode>
    <LookupCostCentreCode>LT</LookupCostCentreCode>
    <LookupNominalCode>1003</LookupNominalCode>
    </MatterRevenueCode>
    <MatterStatementTo>
    <MatterStatementOtherName>
    <LookupNameRSN>1526</LookupNameRSN>
    </MatterStatementOtherName>
    </MatterStatementTo>
    <MatterBillTo>
    <MatterBillOtherName>
    <LookupNameRSN>635</LookupNameRSN>
    </MatterBillOtherName>
    </MatterBillTo>
    <MatterBanks>
    <LookupOfficeBankId>NWO</LookupOfficeBankId>
    <LookupClientBankId>NWC</LookupClientBankId>
    <LookupDepositBankId>NWDD</LookupDepositBankId>
    <LookupStakeholderBankId>SH</LookupStakeholderBankId>
    </MatterBanks>
    </Matter>
    <LookupCaseModuleCode>NDM</LookupCaseModuleCode>
    <LookupCaseTypeCode>NDM</LookupCaseTypeCode>
    <StartPosition>
    <EventDrivenCase>
    <StartEvent>
    <LookupEventCode>COBB1</LookupEventCode>
    </StartEvent>
    </EventDrivenCase>
    </StartPosition>
    <CaseInformation>
    <!--( Client ) Lookup Details-->
    <Section SectionCode="CL">
    <InformationLine LineNumber = "1">
    <AnswerName>
    <LookupNameRSN>102</LookupNameRSN>
    </AnswerName>
    </InformationLine>
    </Section>
    <!-- Defendant Details -->
    <Section SectionCode="D">
    <InformationLine LineNumber="1">
    <AnswerAlpha>Accounts</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="3">
    <AnswerAlpha>Mark Cobb Ltd</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="5">
    <AnswerAlpha>123 Humpty Way</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="7">
    <AnswerAlpha>Upper Lower Street</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="9">
    <AnswerAlpha>London</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="11">
    <AnswerAlpha>SE15 6TR</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="17">
    <AnswerAlpha>01892 630000</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="18">
    <AnswerAlpha>X</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="20">
    <AnswerAlpha>markcobb@yahoo.com</AnswerAlpha>
    </InformationLine>
    </Section>
    <!-- Supplier Details -->
    <Section SectionCode="SP">
    <InformationLine LineNumber="3">
    <AnswerAlpha>Jack Jones Ltd</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="4">
    <AnswerAlpha>Bob Hamiliton</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="5">
    <AnswerAlpha>Unit B, Lower Marsh</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="6">
    <AnswerAlpha>Gilbert Way</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="7">
    <AnswerAlpha>Manchester</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="8">
    <AnswerAlpha>M17 2IB</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="10">
    <AnswerAlpha>0161 223344</AnswerAlpha>
    </InformationLine>
    </Section>
    <Section SectionCode="CBD">
    <InformationLine LineNumber="1">
    <AnswerAlpha>Barclays Bank plc</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="2">
    <AnswerAlpha>20-20-20</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="3">
    <AnswerAlpha>11111111</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="4">
    <AnswerAlpha>IBAN Number</AnswerAlpha>
    </InformationLine>
    <InformationLine LineNumber="5">
    <AnswerAlpha>XXXXX</AnswerAlpha>
    </InformationLine>
    </Section>
    </CaseInformation>
    <Debt>
    <Stages>
    <PresuingStage>
    <DebtInvoice>
    <InvoiceDate>2010-08-23</InvoiceDate>
    <OverdueDate>2010-09-22</OverdueDate>
    <InvoiceNumber>12345</InvoiceNumber>
    <InvoiceReference>Delivery 1</InvoiceReference>
    <InvoiceDescription>INVOICE</InvoiceDescription>
    <InvoiceAmount>50.00</InvoiceAmount>
    <DebtChargeInterest>
    <Statutory/>
    </DebtChargeInterest>
    </DebtInvoice>
    <DebtInvoice>
    <InvoiceDate>2010-09-16</InvoiceDate>
    <OverdueDate>2010-10-16</OverdueDate>
    <InvoiceNumber>23456</InvoiceNumber>
    <InvoiceReference>Delivery 2</InvoiceReference>
    <InvoiceDescription>INVOICE</InvoiceDescription>
    <InvoiceAmount>60.00</InvoiceAmount>
    <DebtChargeInterest>
    <Statutory/>
    </DebtChargeInterest>
    </DebtInvoice>
    <DebtInvoice>
    <InvoiceDate>2010-09-16</InvoiceDate>
    <OverdueDate>2010-10-16</OverdueDate>
    <InvoiceNumber>34567</InvoiceNumber>
    <InvoiceReference>Delivery 3</InvoiceReference>
    <InvoiceDescription>INVOICE</InvoiceDescription>
    <InvoiceAmount>70.00</InvoiceAmount>
    <DebtChargeInterest>
    <Statutory/>
    </DebtChargeInterest>
    </DebtInvoice>
    <DebtInvoice>
    <InvoiceDate>2010-09-22</InvoiceDate>
    <OverdueDate>2010-10-22</OverdueDate>
    <InvoiceNumber>45678</InvoiceNumber>
    <InvoiceReference>Delivery 4</InvoiceReference>
    <InvoiceDescription>INVOICE</InvoiceDescription>
    <InvoiceAmount>80.00</InvoiceAmount>
    <DebtChargeInterest>
    <Statutory/>
    </DebtChargeInterest>
    </DebtInvoice>
    <DebtPayment>
    <TypeOfCredit>credit note</TypeOfCredit>
    <Date>2010-08-23</Date>
    <Reference>C5667</Reference>
    <Description>CREDIT NOTE</Description>
    <Amount>36.19</Amount>
    <PaidTo>claimant</PaidTo>
    <Held>no</Held>
    </DebtPayment>
    <DebtPayment>
    <TypeOfCredit>credit note</TypeOfCredit>
    <Date>2010-10-04</Date>
    <Reference>C5668</Reference>
    <Description>CREDIT NOTE</Description>
    <Amount>19.74</Amount>
    <PaidTo>claimant</PaidTo>
    <Held>no</Held>
    </DebtPayment>
    </PresuingStage>
    </Stages>
    </Debt>
    </Case>
    </GalaxyLegal>
  2. Luis Martin Moderator

    Did you find any solution?
  3. satya Moderator

Share This Page