XML file using T-SQL

• UnionAll

  SSCrazy

  Points: 2102

  More actions

  May 5, 2011 at 7:39 am

  #235827

  Hello, I need to create a xml file with sub master tags. Below is the script. Please provide any help!

  Table Script:

  CREATE TABLE #Customer

  (CustID INT, CustLastName VARCHAR(30), DOB DATETIME

  , Addr1 VARCHAR(30), Addr2 VARCHAR(30), City VARCHAR(30))

  INSERT INTO #Customer

  VALUES (1001,'Smith','2011-05-05 09:25:48.253'

  ,'100 Smith St.',NULL,'New York')

  SELECT *

  FROM #Customer

  And the xml file should look like:

  <CustomerDetails>

  <CustID>1001</CustID>

  <CustLastName>Smith</CustLastName>

  <DOB>2011-05-05T09:25:48.253</DOB>

  <Address>

  <Addr1>100 Smith St.</Addr1>

  <City>New York</City>

  </Address>

  </CustomerDetails>

• Mike01

  SSChampion

  Points: 11381

  More actions

  May 5, 2011 at 8:07 am

  #1320421

  CREATE TABLE #Customer

  (CustID INT, CustLastName VARCHAR(30), DOB DATETIME

  , Addr1 VARCHAR(30), Addr2 VARCHAR(30), City VARCHAR(30))

  INSERT INTO #Customer

  VALUES (1001,'Smith','2011-05-05 09:25:48.253'

  ,'100 Smith St.',NULL,'New York')

  SELECT CustID,

  CustLastName,

  DOB,

  Addr1 as 'Address/Addr1',

  City as 'Address/City'

  FROM #Customer

  for xml path('CustomerDetails')

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/