I want to get the contents of a sql table using xml:
projectID - projectName - customerID -customerName - city
i want to list all columns in xml tags and nest the customer seperately inside a project element
how can i then use this in .net correctly?
You can transform the table row into XML using by using xmlelement:
(select xmlelement (name Project,
xmlattributes(p.projectID as id),
xmlelement(p.name as Name),
xmlelement(name Customer,
xmlattributes(p.customerID as id),
xmlforest(p.customerName as Name, p.city as City)
))
)
from
TableName p
You need to replace 'TableName' with whatever your table is called. This basically returns data as XML and nests a new customer element inside a parent project tag. This is the XML outputted:
<Project id="1">
<Project Name>Manhatten Project</Project>
<Customer id="200">
<Name>Jim Doe</Name>
<City>New York</City>
</Customer>
</Project>
You can then parse the XML in .net with the Read() method. If you've not used XML with .net before, read this article for a general introduction.
