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

  • How can I insert records from one table into second table
  • sql invalid queries from a csv file
  • TSQL parition in fixed number of rows
  • SQL SSIS handling of quotation marks in content - 2008R2 vs. 2014 difference?
  • SQL reformat a linked list (a list of locations: nodes -> edges)
  • Select Max And Inner Join
  • how can i then use this in .net correctly?

  • How to send image to web service node js and then execute stored procedure with this image parameter
  • How to Insert auto-generated identity value as part of a hierarchyid field on the same record
  • How to read/export XML file which is output from SQL server in JAVA
  • Select statements cannot return data to a client
  • How to convert list of numbers into (temp) table using SQL (SQL Server)
  • SQL Server query with update error from select query
  • 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)
       ))
    )
    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.

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