asked
FOR XML in SQLSERVER 2008 need help
your company's database contains customers and ordes tables. you have been asked to wirte a select statement that exposes the data as a valid and well formed xml document. then xml data must be attribute base and order data xml must be nested in the customer data xml. you need to write a select statement to meet the requirements. whcih statement should you use? 1. SELECT c.contactname,o.orderdate,o.requireddate from customers c inner join orders o on c.customerid =o.customerid for xml raw('contact'),root('conctactorderdate') 2.SELECT c.contactname,o.orderdate,o.requireddate from customers c inner join orders o on c.customerid =o.customerid for xml path('contactorderdate') 3.SELECT c.contactname,o.orderdate,o.requireddate from customers c inner join orders o on c.customerid =o.customerid for xml auto 4.SELECT c.contactname,o.orderdate,o.requireddate from customers c inner join orders o on c.customerid =o.customerid for xml auto,root('contactorderdate') According to the requirement the option 3 seems to be correct. can anyone tell me the correct answer? thanks lakshmi nair
No, #4 is correct. To be well-formed the XML must have a root tag. #2 & #3 do not have root tags. #1 is incorrect because the order data is not nested in the customer data. You can also achieve the desired result with nested FOR XML PATH queries: SELECT c.ContactName AS "@ContactName" ,(SELECT o.OrderDate AS "@OrderDate" ,o.RequiredDate AS "@RequiredDate" FROM Orders o WHERE (c.CustomerID = o.CustomerID ) FOR XML PATH('o'), TYPE) FROM Customers c FOR XML PATH('c'), ROOT('ContactOrderDate')
