locked
Using FOR XML AUTO with an OleDB Command in C# RRS feed

  • Question

  • User780052763 posted
    I have a few questions actually.

    1. The Application I am working on is being developed to work with SQL Server, However in the future we may want to port it to use on another DB say Sybase ASA, or Oracle. The application is built using 3-Tier Arch so hopefully only the DataAccess layer should need to be altered if we go for this change.
    I am wondering the most generic yet still robust class library to use , i.e. System.Data.sql or System.Data.QleDb or whatever. By using one or the other do I limit myself as to what DB I can access in the future?

    2. At the moment I am going to use the System.Data.OleDb class for my data access (unless you have a good answer to the above question) I would like to return XML from a table using a stored procedure and the FOR XML AUTO function. The only samples I can find doing this are using the SQL provider or the microsoft managed classes, which seem great but that brings me back to question 1, if I use these classes do I limit myself as to which database I can use?

    OK only two questions, but if you could offer any answers/advice that would be great.
    Thanks for reading
    Jeremy
    Wednesday, August 10, 2005 12:39 PM

All replies

  • User1416329745 posted
    Oracle, Sybase and SQL Server 2000 XML implementation are not the same so you cannot use FOR XML AUTO but you can use the code from the link below which uses OPENXML because current  SQL Server  will not decompose your XML while .NET XML classes will decompose it.  This is just to get you started you shoud look up XML implementation in all three databases.  Hope this helps.

    http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx
    Wednesday, August 10, 2005 5:01 PM
  • User-323328378 posted

    Hi All,
    after half day I finally found an easy but not strongly tested solution.
    If u have a statement like this:

    SELECT A,B,C

    FROM MYTABLE

    FOR XML RAW('row_node_name'), ROOT('root_node_name'), ELEMENTS

    try to use to following approach:

    Declare @Data as xml 

    set @Data = (  

    SELECT A,B,C

    FROM MYTABLE

    FOR XML RAW('row_node_name'), ROOT('root_node_name'), ELEMENTS

         )

    SELECT @Data 

    You can run it by a standard OleDbCommand.
    When I sad "not strongly tested" I mean that I've not idea about possible limitations regarding retrieved flows.
    If someone has suggestions, considerations or something else please contact me.
    I hope you'll find it usefull.

    Good luck to everybody and see you soon.

    Thursday, November 22, 2012 9:38 AM