This must be written in simple SQL. Write a SELECT statement that returns an XML

Question

This must be written in simple SQL.

Write a SELECT statement that returns an XML document that contains all of the invoices from the Invoices table that have more than one line item. This document should include one element for each of these columns: InvoiceNumber, InvoiceDate, InvoiceTotal, InvoiceLineItemDescription, and InvoiceLineItemAmount. Then, save the XML document that’s returned in a filed named MultipleLineItems.xml. Finally, generate an XML schema for the file and save it in a file named MultipleLineItems.xsd.

The data needs to come from two tables: InvoiceLineItems and Invoices. Below are the tables. I can't figure out how to make it pull the invoices that have more than one line item.

1 USE AP 3 SELECT FROM Invoice tems 5 SELECT FROM Invoices 100% Results Messages InvoiceID invoicesequence AccountNo invoiceLineltemAmount InvoiceLineltemDescription 400 85.31 Book copy 52.25 Freight 572 579.42 Catalog ad 59.97 Freight 101 30.75 Freight 102 20551.18 CICS book printing 105 503.20 Bronco lease 110 90.36 Card deck advertising 10976.06 11 VSAM book printing 10 113 510 224.00 Health Insurance InvoicelD vendor D InvoiceNumber InvoiceDate Invoice Total Payment Total Credit Total TemslD lnvoiceDueDate Payment Date 000 2012-03-10 00:00:00 85.31 2012-04-09 00:00:00 NULL 2012-04-17 00:00:00 NULL 963253264 2012-03-18 00:00:00 52.25 0.00 0.00 31361833 2012-03-21 00:00:00 579.42 2012-04-10 00:00:00 NULL 123 263253268 2012-03-21 00:00:00 59.97 0.00 0.00 101 263253273 2012-03-22 00:00:00 30.75 0.00 0.00 2012-04-20 00:00:00 NULL 2012-04-21 00:00:00 NULL 110 Po608 2012-03-23 00:00:00 20551.18 000 1200.00 3 2012-04-22 00:00:00 NULL 105 106 9982771 2012-03-24 00:00:00 503.20 2012-04-23 00:00:00 NULL 110 134116 2012-04-17 00:00:00 NULL 2012-03-28 00:00:00 90.36 110 02436 2012-03-31 00:00:00 10976.06 0.00 000 11 2012-04-30 00:00:00 NULL 10 113 547480102 2012-0401 00:00:00 224.00 0.00 0.00 37 2012-04-30 00:00:00 NULL

Explanation / Answer

Write the query in this way.. it will multiple lines of two tables entire data in terms of xml raw data.

SELECT (
SELECT *
FROM InvoiceLineIems
) AS invoiceItems,
(
SELECT *
FROM Invoices
) AS invoices FOR XML RAW;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.