SQL server table to XML

All Replies

• Thursday, March 21, 2013 3:08 AM

   

   
  

  0

  Hi,

  Try with FOR XML Clause at the end of your SELECT statement - http://sathyadb.blogspot.in/2012/09/handling-xml-data-in-sql-server.html

  DECLARE @Employee TABLE (ID INT,CustomerID INT,Address VARCHAR(100),Phone VARCHAR(50),Amount INT,LastUpdated VARCHAR(50))
  INSERT @Employee SELECT 1234,12345,'MD','325.991.4343',1233434,'2013-02-02'
  select ID "@id",
   CustomerID "fieldid/@CustomerID",
   Address "fieldid/@Address",
   Phone "fieldid/@Phone",
   Amount "fieldid/@Amount",
   LastUpdated "fieldid/@LastUpdated"
   from @Employee FOR XML PATH('record')

  
  

  ---

  Thanks & Regards, sathya

  
  

  • Edited by SathyanarrayananS Thursday, March 21, 2013 3:26 AM
  • Proposed As Answer by SathyanarrayananS Sunday, March 24, 2013 7:08 AM
  • Marked As Answer by Iric WenModerator Friday, March 29, 2013 7:16 AM
  • Unmarked As Answer by Navind Monday, April 15, 2013 5:39 AM
  • Unproposed As Answer by Navind Monday, April 15, 2013 5:39 AM
  •  
  • Reply
  • Quote

   
• Friday, April 26, 2013 3:17 PM

   

   
  

  0

  Hi Navid,

  sorry for late reply,i missed your post,try below code for exact XML format...

  DECLARE @Employee TABLE (ID INT,
  CustomerID INT,
  Address VARCHAR(100),
  Phone VARCHAR(50),
  Amount INT,
  LastUpdated VARCHAR(50))
  INSERT @Employee SELECT 1234,12345,'MD','325.991.4343',1233434,'2013-02-02'
  INSERT @Employee SELECT 1234,12345,'MD','325.991.4343',1233434,'2013-02-02'
  DECLARE @xmldata XML
  SET @xmldata = (SELECT CustomerID,Address,Phone,Amount,LastUpdated FROM @Employee FOR XML PATH (''))
  SET @xmldata = (
  SELECT ColumnName AS "@Id",
         ColumnValue AS "text()"
  FROM(
  SELECT i.value('local-name(.)','varchar(100)') ColumnName,
         i.value('.','varchar(100)') ColumnValue
  FROM @xmldata.nodes('//*[text()]') x(i)) tmp
  FOR XML PATH ('field'),root('fields'))
  DECLARE @root XML
  SET @root = (SELECT ID AS "@Id" FROM @Employee FOR XML PATH('record'))
       
  SET @root.modify('       
  insert sql:variable("@xmldata")        
  as last into (/record)[1]') 
  SELECT @root

  XML from above code,

  <record Id="1234">
    <fields>
      <field Id="CustomerID">12345</field>
      <field Id="Address">MD</field>
      <field Id="Phone">325.991.4343</field>
      <field Id="Amount">1233434</field>
      <field Id="LastUpdated">2013-02-02</field>
    </fields>
  </record>

  
  

  ---

  sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

  • Proposed As Answer by SathyanarrayananS Friday, April 26, 2013 3:17 PM
  • Marked As Answer by Kalman TothMicrosoft Community Contributor, Moderator Friday, May 03, 2013 1:36 PM
  •  
  • Reply
  • Quote