CODEWITHSUNDEEP
sqlxml
Krystianya
Krystianya

Reputation: 171

XML File Won't Generate in SQL

I'm attempting to create an XML file if invoices and I believe I have the code correct, but it's not generating the XML file. I've googled, but don't seem to have the correct keywords to find my issue since I'm not sure what the issue is. What could be causing the XML file to not be created?

USE AP

SELECT 
    InvoiceNumber, 
    InvoiceDate, 
    InvoiceTotal, 
    InvoiceLineItemDescription, 
    InvoiceLineItemAmount
        FROM 
            Invoices JOIN InvoiceLineItems
        ON 
            Invoices.InvoiceID = InvoiceLineItems.InvoiceID 
    WHERE Invoices.InvoiceID IN (
        SELECT Invoices.InvoiceID
            FROM Invoices JOIN InvoiceLineItems
         ON Invoices.InvoiceID = InvoiceLineItems.InvoiceID 
    GROUP BY Invoices.InvoiceID
    HAVING COUNT(InvoiceLineItemDescription) > 1)

ORDER BY InvoiceDate 

FOR XML PATH ('InvoiceNumber'), ROOT('Invoices');

This is what I get when I execute the query:

enter image description here

This is the sample data:

enter image description here

When I take out the "HAVING COUNT(InvoiceLineItemDescription) > 1" code it creates the XML file, but I want to only pull invoices that have line items. Nothing I've tried is making it work. Any thoughts?

Upvotes: 1

Views: 148

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

I do not know enough about your data, but the answer might be very simple:

HAVING COUNT(InvoiceLineItemDescription) > 0)

You checked for >1 while you were grouping by InvoiceId. If my magic crystall ball works better than your freaked out computer :-D, I assume, that you have one InvoiceNumber per InvoiceId and there is exactly one InvoiceLineItemDescription per Invoice. With >1 you'd find 2 or more, but not 1...

Upvotes: 2

Related Questions