T-SQL and XML output

Question

So I have written on the joins and left joins etc.. for this xml project I am working on. However I am having some hell of a time with the XML portion. I can get all the data into a dataset. However I can't get the data to output the xml the way I would like to.

The TransactionTable left joins with the TimeEntry table on the transactionID . 1 to many the TransactionTable inner joins to the ClientTable 1 to 1

Data all comes out nicely with the left and inner join on my select statement. All data is there.

However I need it to output in the XML format I need. Can't figure out how to do it. Got close but everytime I get close I run into another roadblock.

see picture attached as well

<receivableInvoices>
      <receivableInvoice refId="RECEIVABLEINVOICE-REFID-123">
        <customerCompanyName>Acme Corp</customerCompanyName>
        <customerEmailAddress>jane.doe@acme.com</customerEmailAddress>
        <invoiceNumber>123456</invoiceNumber>
        <invoiceDate>2014-05-01</invoiceDate>
        <billTo>
           <address>
            <line1>Acme Corp</line1>
            <line2>123 Main Street</line2>
            <line3>STE 100</line3>
            <line4>Attn: Shipping</line4>
            <city>Maitland</city>
            <stateProvince>FL</stateProvince>
            <postalCode>32751</postalCode>
            <country>US</country>
          </address>
          <contact>
            <name>Jane Doe</name>
            <phoneNumber>555-555-5555</phoneNumber>
          </contact>
        </billTo>
        <lineItems>
          <lineItem>
            <lineNumber>1</lineNumber>
            <hours>75.00</hours>
            <description>Description of the line item goes here.</description>
          </lineItem>
        </lineItems>
      </receivableInvoice>
<receivableInvoices>

UPDATE: HERE is a sample query I am running

SELECT
            tt.TransactionID as transactionID,
            c.ClientID as customerRefID,
            c.ClientCompany as customerCompanyName,
            c.clientEmail as customerEmailAddress,
            tt.TransactionInvNum as invoiceNumber,
            tt.InvoiceDate as invoiceDate,
            DATEADD(d,30,tt.InvoiceDate) as dueDate,
            tt.TransactionInvBillAmt as totalAmount,
            tt.TransactionInvBillAmt as balance,
            'USD' as currencyCode,
            'Invoice from Customer X' as description,
            '30' as terms,
            right(tt.PRojectID, (LEN(tt.projectid) - charindex(':',tt.projectid))) as purchaseOrderNumber,
            right(tt.PRojectID, (LEN(tt.projectid) - charindex(':',tt.projectid))) as salesOrderNumber,
            c.ClientCompany as "shipTo/address/line1",
            c.ClientStreet as "shipTo/address/line2",
            c.ClientStreet2 as "shipTo/address/line3",
            c.ClientCity as "shipTo/address/city",
            c.ClientState as "shipTo/address/stateProvince",
            c.ClientZip as "shipTo/address/postalCode",
            c.ClientFName + ' ' + c.ClientLName as "shipTo/contact/name",
            c.ClientPhone as "shipTo/contact/phoneNumber",
            c.ClientCompany as "billTo/address/line1",
            c.ClientStreet as "billTo/address/line2",
            c.ClientStreet2 as "billTo/address/line3",
            c.ClientCity as "billTo/address/city",
            c.ClientState as "billTo/address/stateProvince",
            c.ClientZip as "billTo/address/postalCode"
            FROM TransactionTable tt 
            INNER JOIN Client c
            ON c.ClientID = tt.ClientID
            LEFT JOIN timeentry te ON
            te.TransactionID = tt.transactionID
            WHERE tt.PayID is null 

        FOR XML PATH('receivableInvoice'), ROOT('receivableInvoices'), ELEMENTS

Output link Output of SQL

Answer 1

Thanks to everyone. I have finalized what I needed. Please see how I accomplished this below.

SELECT
a.TransactionID as [@refId],
a.ClientID as customerRefId,
a.ClientCompany as customerCompanyName,
a.ClientEmail as customerEmailAddress,
a.TransactionInvNum as invoiceNumber,
cast(a.InvoiceDate as DATE) as invoiceDate,
cast(a.TransactionInvBillAmt as decimal(8,2)) as totalAmount,
cast(a.TransactionInvBillAmt as decimal(8,2)) as balance,
cast(DATEADD(day, COALESCE(a.GraceDays, 0), a.InvoiceDate) as DATE) as dueDate,
COALESCE(REPLACE(SUBSTRING(a.Country, CHARINDEX('(', a.Country)+1, LEN(RTRIM(a.Country))), ')', ''), 'USD') as currencyCode,
a.ProjectPhase as purchaseOrderNumber,
a.ProjectPhase as salesOrderNumber,

-- BILL TO SECTION (Client Address)
a.ClientCompany                 as "billTo/address/line1",
a.ClientStreet                  as "billTo/address/line2",
a.ClientStreet2                 as "billTo/address/line3",
'Attn: Shipping'                as "billTo/address/line4",
a.ClientCity                    as "billTo/address/city",
a.ClientState                   as "billTo/address/stateProvince",
a.ClientZip                     as "billTo/address/postalCode",
a.ClientCountry                 as "billTo/address/country",
a.ClientFName+' '+a.ClientLName as "billTo/address/contact/name",
LEFT(a.ClientPhone,3) + '-' + SUBSTRING(a.ClientPhone,4,3) + '-' + RIGHT(a.ClientPhone,4)
                                as "billTo/address/contact/phoneNumber",

-- REMIT TO SECTION (Client Address)
a.ClientCompany                 as "remitTo/address/line1",
a.ClientStreet                  as "remitTo/address/line2",
a.ClientStreet2                 as "remitTo/address/line3",
'Attn: Shipping'                as "remitTo/address/line4",
a.ClientCity                    as "remitTo/address/city",
a.ClientState                   as "remitTo/address/stateProvince",
a.ClientZip                     as "remitTo/address/postalCode",
a.ClientCountry                 as "remitTo/address/country",
a.ClientFName+' '+a.ClientLName as "remitTo/address/contact/name",
LEFT(a.ClientPhone,3) + '-' + SUBSTRING(a.ClientPhone,4,3) + '-' + RIGHT(a.ClientPhone,4)
                                as "remitTo/address/contact/phoneNumber",

(
    SELECT 
        ct.LineNumber                                       as lineNumber,
        cast((ct.TEHours * ct.TEBillRate) as decimal(8,2))  as totalAmount,
        ct.TEDescription                                    as description,
        p.ProjectPhase                                      as purchaseOrderNumber,
        ct.LineNumber                                       as purchaseOrderLineNumber,
        p.ProjectPhase                                      as salesOrderNumber,
        ct.LineNumber                                       as salesOrderLineNumber,
        p.ProjectPhase                                      as vendorItemNumber,
        p.ClientID                                          as customerItemNumber,
        p.ProjectName                                       as project,
        0                                                   as taxAmount,
        cast(ct.TEDate as date)                             as taxDate
    FROM TimeEntry as ct
    INNER JOIN PRoject p 
        ON p.ProjectID = ct.ProjectID
    WHERE a.TransactionID = ct.TransactionID
    ORDER BY ct.LineNumber
    FOR XML PATH('LineItem'), ROOT('LineItems'), TYPE
) 

-- Sub Query so that we can get all the line entries from one invoice under the same invoice node
FROM (
    SELECT 
        tt.InvoiceDate, 
        tt.TransactionID, 
        c.ClientStreet, 
        c.ClientStreet2, 
        c.ClientCity, 
        c.ClientState, 
        c.ClientZip, 
        c.ClientCountry, 
        tt.TransactionInvNum, 
        c.ClientID, 
        c.ClientCompany, 
        c.ClientEmail, 
        t.Name, 
        t.GraceDays, 
        tt.TransactionInvBillAmt, 
        cm.Country, 
        tt.ProjectPO, 
        c.ClientFName, 
        c.ClientLName, 
        c.ClientPhone,
        p.ProjectPhase
    FROM TransactionTable tt 
    INNER JOIN Client c 
        ON tt.ClientID = c.ClientID 
    INNER JOIN Project P 
        ON p.ProjectID = tt.ProjectID 
    LEFT JOIN TermsTable t 
        ON tt.TermID = t.TermID 
    LEFT JOIN  CurrencyMultiplier cm 
        ON cm.CurrencyID = c.CurrencyID 
    WHERE 
        tt.PayID is null 
        AND tt.InvoiceDate BETWEEN @startDate AND @endDate
        AND tt.TransactionInvNum LIKE '%' + @invNum + '%'
) a

        FOR XML PATH('recievableInvoice'), ROOT( 'recievableInvoices')

Answer 2

Try this you may need to tweek the names or something i removed the join with the timeentry

    SELECTtime
tt.TransactionID as transactionID,
c.ClientID as customerRefID,
c.ClientCompany as customerCompanyName,
c.clientEmail as customerEmailAddress,
tt.TransactionInvNum as invoiceNumber,
tt.InvoiceDate as invoiceDate,
DATEADD(d,30,tt.InvoiceDate) as dueDate,
tt.TransactionInvBillAmt as totalAmount,
tt.TransactionInvBillAmt as balance,
'USD' as currencyCode,
'Invoice from Customer X' as description,
'30' as terms,
right(tt.PRojectID, (LEN(tt.projectid) - charindex(':',tt.projectid))) as purchaseOrderNumber,
right(tt.PRojectID, (LEN(tt.projectid) - charindex(':',tt.projectid))) as salesOrderNumber,
c.ClientCompany as "shipTo/address/line1",
c.ClientStreet as "shipTo/address/line2",
c.ClientStreet2 as "shipTo/address/line3",
c.ClientCity as "shipTo/address/city",
c.ClientState as "shipTo/address/stateProvince",
c.ClientZip as "shipTo/address/postalCode",
c.ClientFName + ' ' + c.ClientLName as "shipTo/contact/name",
c.ClientPhone as "shipTo/contact/phoneNumber",
c.ClientCompany as "billTo/address/line1",
c.ClientStreet as "billTo/address/line2",
c.ClientStreet2 as "billTo/address/line3",
c.ClientCity as "billTo/address/city",
c.ClientState as "billTo/address/stateProvince",
c.ClientZip as "billTo/address/postalCode",
cast(
  (  
    Select 
     te.lineNumber,
     te.hours,
     te.description
   From    timeentry te
 where te.TransactionID = tt.transactionID
 FOR XML PATH('lineItem'), ROOT('lineItems'), ELEMENTS ) as xml).query('.')

FROM TransactionTable tt 
INNER JOIN Client c
ON c.ClientID = tt.ClientID
WHERE tt.PayID is null 

FOR XML PATH('receivableInvoice'), ROOT('receivableInvoices'), ELEMENTS

Answer 3

Straight join isn't efficient in this case, it's better to use nested FOR XML subqueries:

SELECT
    tt.TransactionID as transactionID,
    c.ClientID as customerRefID, (
        select te.Id as [lineNumber],
            te.Description as [Description]
        from dbo.timeentry te
        where te.TransactionID = tt.transactionID
        for xml path('lineItem'), root('lineItems'), elements, type
    )
FROM dbo.TransactionTable tt 
    INNER JOIN dbo.Client c ON c.ClientID = tt.ClientID
WHERE tt.PayID is null 
FOR XML PATH('receivableInvoice'), ROOT('receivableInvoices'), ELEMENTS, type;

Answer 4

Yo can use to output the result to xml use after the query

SELECT .... FROM TABLE1 INNER JOIN TABLE2 ...
FOR XML PATH('receivableInvoices')

also for the element child you can use for example

SELECT .... 
 city AS "billTo/address/city" 
 .... 

i don't have your query so i can post the statement for you.

for the attribute use

SELECT field_name AS "@refId"  ...