SQL

convert sql query to xml format

-- Use FOR XML PATH('Customer')

SELECT 
   CustomerID as "@CustomerID",
   CompanyName,
   Address as "address/street",
   City as "address/city",
   Region as "address/region",
   PostalCode as "address/zip",
   Country as "address/country",
   ContactName as "contact/name",
   ContactTitle as "contact/title",
   Phone as "contact/phone", 
   Fax as "contact/fax"
FROM Customers
FOR XML PATH('Customer')

-- will result in

  <Customer CustomerID="ALFKI">
    <CompanyName>Alfreds Futterkiste</CompanyName>
    <address>
      <street>Obere Str. 57</street>
      <city>Berlin</city>
      <zip>12209</zip>
      <country>Germany</country>
    </address>
    <contact>
      <name>Maria Anders</name>
      <title>Sales Representative</title>
      <phone>030-0074321</phone>
      <fax>030-0076545</fax>
    </contact>
  </Customer>

Extract Data from SQL Server into XML Format

--SQL Code extract to XML format w/ more explicite and well formed tags than just using "For XML Auto"
    SELECT 
        [EmployeeID] AS '@ID',
        [LastName], [FirstName],
        [Title],
        [BirthDate], [HireDate]
    FROM 
        [dbo].[Employees]
    FOR XML PATH('Employee'), ROOT('Employees')


--Output of This SQL below
    <Employees>
      <Employee ID="1">
        <LastName>Davolio</LastName>
        <FirstName>Nancy</FirstName>
        <Title>Sales Representative</Title>
        <BirthDate>1948-12-08T00:00:00</BirthDate>
        <HireDate>1992-05-01T00:00:00</HireDate>
      </Employee>
      <Employee ID="2">
        <LastName>Fuller</LastName>
        <FirstName>Andrew</FirstName>
        <Title>Vice President, Sales</Title>
        <BirthDate>1952-02-19T00:00:00</BirthDate>
        <HireDate>1992-08-14T00:00:00</HireDate>
      </Employee>