Sql database to pass as XML data

• rtalwar

  SSC Journeyman

  Points: 97

  More actions

  June 18, 2003 at 12:09 pm

  #81070

  HI All.

  I work primarily in XML and XSLT and totally new in SQL.

  Here is some thing i am needing to do.

  I have a SQL database. I need to retrieve data from internal access and then transfer it to XML. Once i have the XML data, then i need to send the retrieved data or recordset to be shown on a www. page.

  I would appreciate if anyone has done some thing similar and can guide me in the right direction.

  Thanks in advance.

  Rahul

• rtalwar

  SSC Journeyman

  Points: 97

  More actions

  June 24, 2003 at 6:49 am

  #462797

  hi,

  Is there anyone who can help me with my query in SQL?

  Or am i in the wrong discussion threads?

• ckempste

  SSCoach

  Points: 17983

  More actions

  June 24, 2003 at 7:13 pm

  #462904

  Hi there

  Its very simple, lookup BOL on "FOR XML" and have a careful read, the developers here use FOR XML RAW and whack it directly into the MS DOM for processing etc and passthrough to apply a style sheet to it. All works a treat.

  4guysfromrolla is a good website for examples.

  Cheers

  Ck

  Chris Kempster

  http://www.chriskempster.com

  Author of "SQL Server 2k for the Oracle DBA"

  
  Chris Kempster
  www.chriskempster.com
  Author of "SQL Server Backup, Recovery & Troubleshooting"
  Author of "SQL Server 2k for the Oracle DBA"

• Melville

  SSCommitted

  Points: 1985

  More actions

  June 26, 2003 at 3:04 am

  #463186

  There are 3 'for xml' (explicit, raw and auto) options. The simplest of which if auto.

  A simple example would be

  create procedure usp_forxml

  (@SupplierID int)

  as

  SELECT [ProductID], [ProductName], [SupplierID] FROM [Northwind].[dbo].[Products] as products where supplierID=@SupplierID

  for xml auto

  Then running...

  usp_forxml @SupplierID=1

  ... should give you the following back from SQL:

  <products ProductID="1" ProductName="Chai" SupplierID="1"/><products ProductID="2" ProductName="Chang" SupplierID="1"/><products ProductID="3" ProductName="Aniseed Syrup" SupplierID="1"/>

  If attributes aren't the way you want to deal with the xml, change the 'for xml auto' to 'for xml auto, elements' and the output would be in the form...

  <products><ProductID>1</ProductID><ProductName>Chai</ProductName><SupplierID>1</SupplierID></products><products><ProductID>2</ProductID><ProductName>Chang</ProductName><SupplierID>1</SupplierID></products><products><ProductID>3</ProductID><ProductName>Aniseed Syrup</ProductName><SupplierID>1</SupplierID></products>

  Its always difficult to know how complicated your requirements are but this may get you started and give you something that works before you tackle BOL.