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 example

<receivableInvoices>
      <receivableInvoice refId="RECEIVABLEINVOICE-REFID-123">
        <customerCompanyName>Acme Corp</customerCompanyName>
        <customerEmailAddress>[email protected]</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

So do you want to put the result of your query into xml instead of rows and columns? — Aladin Hdabe
– Aladin Hdabe, Commented Sep 28, 2014 at 3:41
Correct. I can write all the joins and I have read tons and tons on how XML and T-SQL plays together. However I can't figure out how to break this down — GrafixMastaMD
– GrafixMastaMD, Commented Sep 28, 2014 at 3:46

Aladin Hdabe · Accepted Answer · 2014-09-28 03:55:16Z

1

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

answered Sep 28, 2014 at 3:55

Aladin Hdabe

8756 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GrafixMastaMD Over a year ago

I got this far. But because there is multiple lines for 1 invoice ( meaning multiple timeentries) it duplicates Invoice Nodes. Although it should only be one

GrafixMastaMD Over a year ago

I upped this only because it was a good starting point. Although I already got to this point on my own.

Roger Wolf · Accepted Answer · 2014-09-28 05:01:33Z

1

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;

answered Sep 28, 2014 at 5:01

Roger Wolf

7,7872 gold badges29 silver badges37 bronze badges

2 Comments

GrafixMastaMD Over a year ago

With large amounts of data. How will it no to put the right time entries under the right inv num node with a sub query that's completely independent? I don't see the tie relationship wise from the main query to sub query.

Roger Wolf Over a year ago

@GrafixMastaMD, it's not independent, it's correlated: where te.TransactionID = tt.transactionID The tt alias is defined on the outermost level, and the te alias is in the subquery.

Aladin Hdabe · Accepted Answer · 2014-09-28 05:29:20Z

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

I upped this post only because it helped me understanding nesting of xml types and querying them a bit more.

GrafixMastaMD · Accepted Answer · 2014-09-28 17:46:44Z

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

Collectives™ on Stack Overflow

T-SQL and XML output

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related