SQL TO XML | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL TO XML

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>

Did you find any solution?

There are three modes of format; you can get the XML string output as your result from your SQL Query.
  1. AUTO MODE
    • RAW MODE
      • EXPLICIT MODE

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |