Xml from sql table

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

  • Best way of constructing dynamic sql queries in C#/.NET3.5?
  • Modulo operation on primary key column is too slow
  • SQL Server : SUM() of multiple rows including where clauses
  • How do I control the format in which MS-SQL Server returns datetime values?
  • Split a string with no delimiters into columns
  • Index autoincrement for Microsoft SQL Server 2008 R2
  • how can i then use this in .net correctly?

  • 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code. Additional information: Incorrect syntax near 'b'
  • Is it a bad idea to use GUIDs as primary keys in MS SQL?
  • what is the query to get “related tags” like in stack overflow
  • Selecting column names from a query NOT a view
  • Storing and referencing an immutable ordered list in a relational database
  • Selecting nvarchar 'like' in a left join - inner join combination
  • One Solution collect form web for “Xml from sql table”

    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)
       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>

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.