Question

Write a SELECT statement that returns an XML document that contains all of the invoices in the Invoices table that have more than one line item. This document should include one element for each of the following columns:InvoiceNumberInvoiceDateInvoiceTotalInvoiceLineItemDescriptionInvoiceLineItemAmountHint: Below is the SQL part of the query that you should use so that all you have to add is the XML partSELECT InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceLineItemDescription AS ItemDescription, InvoiceLineItemAmount AS ItemAmountFROM Invoices AS Invoice JOIN InvoiceLineItems AS LineItemON Invoice.InvoiceID = LineItem.InvoiceIDWHERE Invoice.InvoiceID IN (SELECT InvoiceIDFROM InvoiceLineItemsGROUP BY InvoiceID HAVING COUNT(*) > 1)ORDER BY InvoiceDateSave the XML document that is returned in a file named MultipleLineItems.xmlGenerate an XML schema for the file and save it in a file named MultipleLineItems.xsd

Answer 1

Answer:

The code is given, paying attention to every column details

Explanation:

columns:

InvoiceNumber

InvoiceDate

InvoiceTotal

InvoiceLineItemDescription

InvoiceLineItemAmount

SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceLineItemDescription AS ItemDescription, InvoiceLineItemAmount AS ItemAmount

FROM Invoices AS Invoice JOIN InvoiceLineItems AS LineItem

ON Invoice.InvoiceID = LineItem.InvoiceID

WHERE Invoice.InvoiceID IN (

SELECT InvoiceID

FROM InvoiceLineItems

GROUP BY InvoiceID

HAVING COUNT(*) > 1)

ORDER BY InvoiceDate

FOR XML RAW, ELEMENTS;