SQL TO AN XML SCHEMA - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL TO AN XML SCHEMA

Author

Topic

cobby1812
Starting Member

16 Posts

Posted - 2010-12-02 : 16:31:10

Hi there,
I have written a very simple view that collects data from x tables. What I now need to do is create the Transact sql as an xml file that then gets loaded into another system. I have seen an example of the xml file...but I cant get it into my head how I get the sql to then verify against a schema.......and I havent got a clue what this means either.
<AnswerAlpha>9999 9999999</AnswerAlpha>
</InformationLine>
These fields seem to link to lines in my view (telephone number etc......). I just cant get me head round it...any help would be gratefully received.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-02 : 17:16:46

please provide us your DDL(s)
What do you have now as far as query?

If you don't have the passion to help people, you have no passion

cobby1812
Starting Member

16 Posts

Posted - 2010-12-03 : 08:35:41

Hi there,
this is the select query
SELECT Cast(dbo.ClientMaster.ClientNumber as varchar) + '/' + Cast(dbo.DebtorMaster.DebtorNumber as Char) as Reference,
'IGF01' as Description,
dbo.DebtorMaster.Name AS Defendant,
dbo.DebtorMaster.AddressLine1 AS Address1,
dbo.DebtorMaster.AddressLine2 AS Address2,
dbo.DebtorMaster.AddressLine3 AS Address3,
dbo.DebtorMaster.AddressLine4 AS Address4,
dbo.DebtorAccountStatic.PhoneNumber as Telephone,
' ' as emailaddress,
dbo.DebtorAccountStatic.ContactName as Contact,
dbo.OpenItems.DocumentDate as InvoiceDate,
dbo.OpenItems.DueDate as OverdueDate,
dbo.OpenItems.DocumentNumber as InvoiceNumber,
dbo.OpenItems.DocumentReference as InvoiceRef,
dbo.OpenItems.DocumentBalance as Amount,
dbo.DType.FullDesc as InvoiceDescription,
dbo.DType.FullDesc as Type,
dbo.ClientMaster.Name as Supplier,
dbo.ClientAccountStaticA.ClientAccountContact as SuppContact,
dbo.ClientMaster.AddressLine1,
dbo.ClientMaster.AddressLine2,
dbo.ClientMaster.AddressLine3,
dbo.ClientMaster.AddressLine4,
dbo.ClientAccountStaticA.ContactTelephoneNumber,
' ' as Bank,
dbo.ClientAccountStaticB.BankSortCode1 as SortCode,
dbo.ClientAccountStaticB.BankAccountNumber1 as BankAcctNumber,
' ' as IBAN,
' ' as Swift
Shall I post the xml as well.....

cobby1812
Starting Member

16 Posts

Posted - 2010-12-03 : 08:40:46

<?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>xxxxxx/Description>
<CreationDateTime>2010-11-15T12:30:39</CreationDateTime>
<Origin>Company Name Here</Origin>
</FileIdentifier>
<Case>
<Matter>
<LookupClientId>CLO01</LookupClientId>
<AutogenerateMatterId />
<MatterReference>04638/0119751</MatterReference>
<MatterSearch>XXXXXXXXXXXX</MatterSearch>
<MatterDescription>XXXXXXXXX</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>CL01</LookupEventCode>
</StartEvent>
</EventDrivenCase>
</StartPosition>
<CaseInformation>

<Section SectionCode="CL">
<InformationLine LineNumber = "1">
<AnswerName>
<LookupNameRSN>102</LookupNameRSN>
</AnswerName>
</InformationLine>
</Section>

<Section SectionCode="D">
<InformationLine LineNumber="1">
<AnswerAlpha>Accounts</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="3">
<AnswerAlpha>DebtorName</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="5">
<AnswerAlpha>Address</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="7">
<AnswerAlpha>Addressl</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="9">
<AnswerAlpha>Address 2</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="11">
<AnswerAlpha>Address 3</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="17">
<AnswerAlpha>Telephone</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="18">
<AnswerAlpha>X</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="20">
<AnswerAlpha>bbasics@btinternet.com</AnswerAlpha>
</InformationLine>
</Section>

<Section SectionCode="SP">
<InformationLine LineNumber="3">
<AnswerAlpha> Namhere</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="4">
<AnswerAlpha> </AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="5">
<AnswerAlpha></AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="6">
<AnswerAlpha></AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="7">
<AnswerAlpha></AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="8">
<AnswerAlpha>Manchester P</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="10">
<AnswerAlpha>016</AnswerAlpha>
</InformationLine>
</Section>
<Section SectionCode="CBD">
<InformationLine LineNumber="1">
<AnswerAlpha>Barclays Bank plc</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="2">
<AnswerAlpha>sort code</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="3">
<AnswerAlpha>account number</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="4">
<AnswerAlpha>Not Used</AnswerAlpha>
</InformationLine>
<InformationLine LineNumber="5">
<AnswerAlpha>Not used</AnswerAlpha>
</InformationLine>
</Section>
</CaseInformation>
<Debt>
<Stages>
<PresuingStage>
<DebtInvoice>
<InvoiceDate>2010-08-23</InvoiceDate>
<OverdueDate>2010-09-22</OverdueDate>
<InvoiceNumber>111111</InvoiceNumber>
<InvoiceReference>mike lukin</InvoiceReference>
<InvoiceDescription>INVOICE</InvoiceDescription>
<InvoiceAmount>51.70</InvoiceAmount>
<DebtChargeInterest>
<Statutory/>
</DebtChargeInterest>
</DebtInvoice>
<DebtInvoice>
<InvoiceDate>2010-09-16</InvoiceDate>
<OverdueDate>2010-10-16</OverdueDate>
<InvoiceNumber>32688</InvoiceNumber>
<InvoiceReference>JOB21001</InvoiceReference>
<InvoiceDescription>INVOICE</InvoiceDescription>
<InvoiceAmount>352.50</InvoiceAmount>
<DebtChargeInterest>
<Statutory/>
</DebtChargeInterest>
</DebtInvoice>
<DebtInvoice>
<InvoiceDate>2010-09-16</InvoiceDate>
<OverdueDate>2010-10-16</OverdueDate>
<InvoiceNumber>32690</InvoiceNumber>
<InvoiceReference>x c v </InvoiceReference>
<InvoiceDescription>INVOICE</InvoiceDescription>
<InvoiceAmount>493.96</InvoiceAmount>
<DebtChargeInterest>
<Statutory/>
</DebtChargeInterest>
</DebtInvoice>
<DebtInvoice>
<InvoiceDate>2010-09-22</InvoiceDate>
<OverdueDate>2010-10-22</OverdueDate>
<InvoiceNumber11111</InvoiceNumber>
<InvoiceReference>123344</InvoiceReference>
<InvoiceDescription>INVOICE</InvoiceDescription>
<InvoiceAmount>372.48</InvoiceAmount>
<DebtChargeInterest>
<Statutory/>
</DebtChargeInterest>
</DebtInvoice>
<DebtPayment>
<TypeOfCredit>credit note</TypeOfCredit>
<Date>2010-08-23</Date>
<Reference>xxc</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>5312</Reference>
<Description>CREDIT NOTE</Description>
<Amount>19.74</Amount>
<PaidTo>claimant</PaidTo>
<Held>no</Held>
</DebtPayment>
</PresuingStage>
</Stages>
</Debt>
</Case>
</GalaxyLegal>

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-03 : 10:24:31

your query is very different that the xml result


DECLARE @DebtorMaster TABLE(Reference varchar(255),
                            Description varchar(255) , 
                            name varchar(255) , 
                            Defendant varchar(255) , 
                            Address1 varchar(255),  
                            Address2 varchar(255),  
                            Address3 varchar(255),  
                            Address4 varchar(255),
                            Telephone varchar(255) ,
                            emailaddress varchar(255),
                            Contact varchar(255),
                            InvoiceDate datetime , 
							OverdueDate datetime,
							InvoiceNumber INT,  
							InvoiceRef varchar(255),
							Amount DECIMAL(10,2),
							InvoiceDescription varchar(255),
							TYPE varchar(255),
							Supplier varchar(255),
							SuppContact varchar(255),
                            AddressLine1 varchar(255),  
                            AddressLine2 varchar(255),  
                            AddressLine3 varchar(255),  
                            AddressLine4 varchar(255),
                            ContactTelephoneNumber varchar(255),
                            Bank varchar(255),
                            SortCode  varchar(255),
                            BankAcctNumber varchar(255),
                            IBAN varchar(255),
                            Swift varchar(255)
                                                        )
                            

INSERT INTO  @DebtorMaster
        ( Reference ,
          Description ,
          name ,
          Defendant ,
          Address1 ,
          Address2 ,
          Address3 ,
          Address4 ,
          Telephone ,
          emailaddress ,
          Contact ,
          InvoiceDate ,
          OverdueDate ,
          InvoiceNumber ,
          InvoiceRef ,
          Amount ,
          InvoiceDescription ,
          TYPE ,
          Supplier ,
          SuppContact ,
          AddressLine1 ,
          AddressLine2 ,
          AddressLine3 ,
          AddressLine4 ,
          ContactTelephoneNumber ,
          Bank ,
          SortCode ,
          BankAcctNumber ,
          IBAN ,
          Swift
        )
VALUES  ( '12345' , -- Reference - varchar(255)
          'Get Paid' , -- Description - varchar(255)
          'Mark Cobb' , -- name - varchar(255)
          'Gordon Peterson' , -- Defendant - varchar(255)
          'Kingston upon Hull' , -- Address1 - varchar(255)
          'East Riding of Yorkshire' , -- Address2 - varchar(255)
          'Hedon Road' , -- Address3 - varchar(255)
          'HU9 5LS' , -- Address4 - varchar(255)
          '01482 282200' , -- Telephone - varchar(255)
          'HMPrisonService@hmprisonservice.gov.uk' , -- emailaddress - varchar(255)
          'Paul Foweather OBE' , -- Contact - varchar(255)
          '2010-12-03 15:04:04' , -- InvoiceDate - datetime
          '2010-12-03 15:04:04' , -- OverdueDate - datetime
          45676 , -- InvoiceNumber - int
          '45678UTHH' , -- InvoiceRef - varchar(255)
          10000 , -- Amount - decimal
          '' , -- InvoiceDescription - varchar(255)
          'Military' , -- TYPE - varchar(255)
          'GCHQ' , -- Supplier - varchar(255)
          'Julian Assange' , -- SuppContact - varchar(255)
          '1212' , -- AddressLine1 - varchar(255)
          '11212' , -- AddressLine2 - varchar(255)
          '67676' , -- AddressLine3 - varchar(255)
          '87787' , -- AddressLine4 - varchar(255)
          '01482 282400' , -- ContactTelephoneNumber - varchar(255)
          'HSBC' , -- Bank - varchar(255)
          '01' , -- SortCode - varchar(255)
          '76767fhd878' , -- BankAcctNumber - varchar(255)
          'GB35 MIDL 4025 3432 1446 70' , -- IBAN - varchar(255)
          'NOSCVGVG'  -- Swift - varchar(255)
        )
        
        
SELECT  Reference ,
        Description ,
        name ,
        Defendant ,
        Address1 ,
        Address2 ,
        Address3 ,
        Address4 ,
        Telephone ,
        emailaddress ,
        Contact ,
        InvoiceDate ,
        OverdueDate ,
        InvoiceNumber ,
        InvoiceRef ,
        Amount ,
        InvoiceDescription ,
        TYPE ,
        Supplier ,
        SuppContact ,
        AddressLine1 ,
        AddressLine2 ,
        AddressLine3 ,
        AddressLine4 ,
        ContactTelephoneNumber ,
        Bank ,
        SortCode ,
        BankAcctNumber ,
        IBAN ,
        Swift
FROM @DebtorMaster

If you don't have the passion to help people, you have no passion

cobby1812
Starting Member

16 Posts

Posted - 2010-12-03 : 13:48:21

Ok that makes a lot more sense. So doing it this way will allow the data to be output in the xml schema I require. Sory about my sql...there is a lot in there that is just required.....I need to update that.

I will give it a go....

many thanks for your patience and time

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-03 : 13:59:43

no this was a sample way of giving people data to answer your question. this is not the way to do it. please answer this question.

--> what exactly do you want your xml to look like. The sample you showed is different than the query you specified.


        
DECLARE @DebtorMaster TABLE(Reference varchar(255),
                            Description varchar(255) , 
                            name varchar(255) , 
                            Defendant varchar(255) , 
                            Address1 varchar(255),  
                            Address2 varchar(255),  
                            Address3 varchar(255),  
                            Address4 varchar(255),
                            Telephone varchar(255) ,
                            emailaddress varchar(255),
                            Contact varchar(255),
                            InvoiceDate datetime , 
							OverdueDate datetime,
							InvoiceNumber INT,  
							InvoiceRef varchar(255),
							Amount DECIMAL(10,2),
							InvoiceDescription varchar(255),
							TYPE varchar(255),
							Supplier varchar(255),
							SuppContact varchar(255),
                            AddressLine1 varchar(255),  
                            AddressLine2 varchar(255),  
                            AddressLine3 varchar(255),  
                            AddressLine4 varchar(255),
                            ContactTelephoneNumber varchar(255),
                            Bank varchar(255),
                            SortCode  varchar(255),
                            BankAcctNumber varchar(255),
                            IBAN varchar(255),
                            Swift varchar(255)
                                                        )
                            

INSERT INTO  @DebtorMaster
        ( Reference ,
          Description ,
          name ,
          Defendant ,
          Address1 ,
          Address2 ,
          Address3 ,
          Address4 ,
          Telephone ,
          emailaddress ,
          Contact ,
          InvoiceDate ,
          OverdueDate ,
          InvoiceNumber ,
          InvoiceRef ,
          Amount ,
          InvoiceDescription ,
          TYPE ,
          Supplier ,
          SuppContact ,
          AddressLine1 ,
          AddressLine2 ,
          AddressLine3 ,
          AddressLine4 ,
          ContactTelephoneNumber ,
          Bank ,
          SortCode ,
          BankAcctNumber ,
          IBAN ,
          Swift
        )
VALUES  ( '12345' , -- Reference - varchar(255)
          'Get Paid' , -- Description - varchar(255)
          'Mark Cobb' , -- name - varchar(255)
          'Gordon Peterson' , -- Defendant - varchar(255)
          'Kingston upon Hull' , -- Address1 - varchar(255)
          'East Riding of Yorkshire' , -- Address2 - varchar(255)
          'Hedon Road' , -- Address3 - varchar(255)
          'HU9 5LS' , -- Address4 - varchar(255)
          '01482 282200' , -- Telephone - varchar(255)
          'HMPrisonService@hmprisonservice.gov.uk' , -- emailaddress - varchar(255)
          'Paul Foweather OBE' , -- Contact - varchar(255)
          '2010-12-03 15:04:04' , -- InvoiceDate - datetime
          '2010-12-03 15:04:04' , -- OverdueDate - datetime
          45676 , -- InvoiceNumber - int
          '45678UTHH' , -- InvoiceRef - varchar(255)
          10000 , -- Amount - decimal
          '' , -- InvoiceDescription - varchar(255)
          'Military' , -- TYPE - varchar(255)
          'GCHQ' , -- Supplier - varchar(255)
          'Julian Assange' , -- SuppContact - varchar(255)
          '1212' , -- AddressLine1 - varchar(255)
          '11212' , -- AddressLine2 - varchar(255)
          '67676' , -- AddressLine3 - varchar(255)
          '87787' , -- AddressLine4 - varchar(255)
          '01482 282400' , -- ContactTelephoneNumber - varchar(255)
          'HSBC' , -- Bank - varchar(255)
          '01' , -- SortCode - varchar(255)
          '76767fhd878' , -- BankAcctNumber - varchar(255)
          'GB35 MIDL 4025 3432 1446 70' , -- IBAN - varchar(255)
          'NOSCVGVG'  -- Swift - varchar(255)
        )
        
       
;WITH XMLNAMESPACES ('http://www.civica.co.uk/GalaxyLegal GalaxyLegal-v5-3.xsd' as schemaLocation,
                     'http://www.w3.org/2001/XMLSchema-instance' AS xsi,
                     'http://www.civica.co.uk/GalaxyLegal' as MI                   
                    )
SELECT  Reference ,
        Description 'FileIdentifier/Description',
        GETDATE() 'FileIdentifier/CreationDateTime',
        Bank 'FileIdentifier/Origin',
        name 'Case/Matter/name',
        Defendant 'Case/Matter/Defendant',
        Address1 'Case/Matter/Address1',
        Address2 'Case/Matter/Address2',
        Address3 'Case/Matter/Address3',
        Address4 'Case/Matter/Address4',
        Telephone 'Case/Matter/Telephone',
        emailaddress 'Case/Matter/emailaddress',
        Contact 'Case/Matter/Contact',
        InvoiceDate 'Case/Matter/InvoiceDate',
        OverdueDate 'Case/Matter/OverdueDate',
        InvoiceNumber 'Case/Matter/InvoiceNumber',
        InvoiceRef 'Case/Matter/InvoiceRef',
        Amount 'Case/Matter/Amount',
        InvoiceDescription 'Case/Matter/InvoiceDescription',
        [type] 'Case/Matter/type',
        Supplier 'Case/Matter/nameSupplier',
        SuppContact 'Case/Matter/SuppContact',
        AddressLine1 'Case/Matter/AddressLine1',
        AddressLine2 'Case/Matter/AddressLine2',
        AddressLine3 'Case/Matter/AddressLine3',
        AddressLine4 'Case/Matter/AddressLine4',
        ContactTelephoneNumber 'Case/Matter/ContactTelephoneNumber',
        Bank 'Case/Matter/Bank',
        SortCode 'Case/Matter/SortCode',
        BankAcctNumber 'Case/Matter/BankAcctNumber',
        IBAN 'Case/Matter/IBAN',
        Swift 'Case/Matter/Swift'
FROM @DebtorMaster 
FOR XML PATH(''),ROOT('GalaxyLegal');

If you don't have the passion to help people, you have no passion

cobby1812
Starting Member

16 Posts

Posted - 2010-12-06 : 06:54:47

OK,
I have updated the sql (see below). I want the sql results to then fit into the schema. All the information up to the Case Information is a single entry into the schema, everything after would create another node based upon the number of transactions, as shown in the XML (below as well). I hope this makes more sense now.

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

DebtorMaster.DebtorNumber = 113733

<?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>

<Section SectionCode="CL">
<InformationLine LineNumber = "1">
<AnswerName>
<LookupNameRSN>102</LookupNameRSN>
</AnswerName>
</InformationLine>
</Section>

<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>

<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>

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-12-06 : 11:25:49

did you try what was posted? try it out. if you do not you will never learn it

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources