Post reply

Sql database to pass as XML data

  • 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

  • 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?

  • 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"

  • 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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply