How do I use SQL 'FOR XML' with .NET?

Discussion in 'ASP .Net Web Services' started by keith chadwick, Oct 27, 2004.

  1. The system I am converting use a lot of sql that makes use of the for xml
    clause provided for in sql server 2000. Below is an example of how this is
    done in ASP:

    set dataxml=server.CreateObject("MSXML2.DOMDocument.4.0")
    dataxml.setProperty "ServerHTTPRequest", false
    dataxml.async=false

    dim sSQL
    sSQL = "<?xml version=""1.0"" ?><ROOT
    xmlns:sql=""urn:schemas-microsoft-com:xml-sql"">"
    sSQL = sSQL & "<clients><sql:query>select * from client order by name for
    xml auto</sql:query></clients>"
    sSQL = sSQL & "<events><sql:query>select * from event order by name for xml
    auto</sql:query></events>"
    sSQL = sSQL & "</ROOT>"

    set cmd=server.CreateObject("ADODB.COMMAND")
    cmd.ActiveConnection=application("DBCONNECTION")
    cmd.CommandText=sSQL
    cmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
    cmd.Properties("Output Encoding")="ISO-8859-1"
    cmd.Properties("XSL")="myxsltfile.xslt"
    cmd.Properties("Output Stream")=dataxml
    cmd.Execute ,,1024

    In the above example a transformation is being applied and the result being
    placed inset the msxsml object for further processing.
    In other cases we do not immediately apply a xsl and simply set the Output
    Streeam to Response.

    How would I go about this in .NET with the same results?

    Thanks in Advance
    Keith
    keith chadwick, Oct 27, 2004
    #1
    1. Advertising

  2. For doing straight FOR XML queries you'd probably want SqlCommand and the
    "ExecuteXmlReader" function. There are a lot of great articles up on MSDN on
    how XML integrates with ADO.Net.

    The example below is actually using an Xml Template to do the FOR XML query.
    For that you'd have to use the managed classes available as part of SqlXml.
    They are included in the latest release.

    --
    Thanks,
    Irwin

    Irwin Dolobowsky
    Program Manager, SqlXml
    http://blogs.msdn.com/irwando

    This posting is provided "AS IS" with no warranties, and confers no rights.


    "keith chadwick" <> wrote in message
    news:...
    > The system I am converting use a lot of sql that makes use of the for xml
    > clause provided for in sql server 2000. Below is an example of how this
    > is done in ASP:
    >
    > set dataxml=server.CreateObject("MSXML2.DOMDocument.4.0")
    > dataxml.setProperty "ServerHTTPRequest", false
    > dataxml.async=false
    >
    > dim sSQL
    > sSQL = "<?xml version=""1.0"" ?><ROOT
    > xmlns:sql=""urn:schemas-microsoft-com:xml-sql"">"
    > sSQL = sSQL & "<clients><sql:query>select * from client order by name for
    > xml auto</sql:query></clients>"
    > sSQL = sSQL & "<events><sql:query>select * from event order by name for
    > xml auto</sql:query></events>"
    > sSQL = sSQL & "</ROOT>"
    >
    > set cmd=server.CreateObject("ADODB.COMMAND")
    > cmd.ActiveConnection=application("DBCONNECTION")
    > cmd.CommandText=sSQL
    > cmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
    > cmd.Properties("Output Encoding")="ISO-8859-1"
    > cmd.Properties("XSL")="myxsltfile.xslt"
    > cmd.Properties("Output Stream")=dataxml
    > cmd.Execute ,,1024
    >
    > In the above example a transformation is being applied and the result
    > being placed inset the msxsml object for further processing.
    > In other cases we do not immediately apply a xsl and simply set the Output
    > Streeam to Response.
    >
    > How would I go about this in .NET with the same results?
    >
    > Thanks in Advance
    > Keith
    >
    >
    >
    >
    >
    Irwin Dolobowsky [MSFT], Oct 27, 2004
    #2
    1. Advertising

  3. Yes i found that stuff, had forgotten to install the some stuff i needed.

    I have a remaing problem. I have to get my web service to return the xml
    returned from the SQL Server and I can not seem to get from the command
    execute to the point of return 'something' in the web service?

    Any suggestions?
    Keith

    "Irwin Dolobowsky [MSFT]" <> wrote in message
    news:%...
    > For doing straight FOR XML queries you'd probably want SqlCommand and the
    > "ExecuteXmlReader" function. There are a lot of great articles up on MSDN
    > on how XML integrates with ADO.Net.
    >
    > The example below is actually using an Xml Template to do the FOR XML
    > query. For that you'd have to use the managed classes available as part of
    > SqlXml. They are included in the latest release.
    >
    > --
    > Thanks,
    > Irwin
    >
    > Irwin Dolobowsky
    > Program Manager, SqlXml
    > http://blogs.msdn.com/irwando
    >
    > This posting is provided "AS IS" with no warranties, and confers no
    > rights.
    >
    >
    > "keith chadwick" <> wrote in message
    > news:...
    >> The system I am converting use a lot of sql that makes use of the for xml
    >> clause provided for in sql server 2000. Below is an example of how this
    >> is done in ASP:
    >>
    >> set dataxml=server.CreateObject("MSXML2.DOMDocument.4.0")
    >> dataxml.setProperty "ServerHTTPRequest", false
    >> dataxml.async=false
    >>
    >> dim sSQL
    >> sSQL = "<?xml version=""1.0"" ?><ROOT
    >> xmlns:sql=""urn:schemas-microsoft-com:xml-sql"">"
    >> sSQL = sSQL & "<clients><sql:query>select * from client order by name for
    >> xml auto</sql:query></clients>"
    >> sSQL = sSQL & "<events><sql:query>select * from event order by name for
    >> xml auto</sql:query></events>"
    >> sSQL = sSQL & "</ROOT>"
    >>
    >> set cmd=server.CreateObject("ADODB.COMMAND")
    >> cmd.ActiveConnection=application("DBCONNECTION")
    >> cmd.CommandText=sSQL
    >> cmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
    >> cmd.Properties("Output Encoding")="ISO-8859-1"
    >> cmd.Properties("XSL")="myxsltfile.xslt"
    >> cmd.Properties("Output Stream")=dataxml
    >> cmd.Execute ,,1024
    >>
    >> In the above example a transformation is being applied and the result
    >> being placed inset the msxsml object for further processing.
    >> In other cases we do not immediately apply a xsl and simply set the
    >> Output Streeam to Response.
    >>
    >> How would I go about this in .NET with the same results?
    >>
    >> Thanks in Advance
    >> Keith
    >>
    >>
    >>
    >>
    >>

    >
    >
    keith chadwick, Oct 28, 2004
    #3
  4. In one app I did this:

    private System.Xml.XmlDocument GetEntries(string strSQL) {
    System.Xml.XmlTextReader xtr= null;
    System.Xml.XmlDocument doc= null;
    System.Data.SqlClient.SqlConnection dbconn= null;
    try {
    dbconn= GetDbConn();
    System.Data.SqlClient.SqlCommand cmd= new
    System.Data.SqlClient.SqlCommand(strSQL, dbconn);
    cmd.Connection.Open();

    xtr = (System.Xml.XmlTextReader) cmd.ExecuteXmlReader();
    //xtr.WhitespaceHandling = System.Xml.WhitespaceHandling.None;
    doc = new System.Xml.XmlDocument();
    doc.Load(xtr);
    }

    catch (System.Exception e3) {
    // SQL error
    System.Console.WriteLine("Exception: e= " + e3 );
    }

    finally {
    if (xtr!=null) xtr.Close();
    if (dbconn!=null) dbconn.Close();
    }
    return doc;
    }

    Where the query was something like this:

    SELECT 1 AS Tag,
    NULL AS Parent,
    '' as [FaqList!1!],
    NULL as [Category!2!CategoryName!element],
    NULL as [FAQ!3!ID!element],
    NULL as [FAQ!3!timestamp!element],
    NULL as [FAQ!3!Question!element],
    NULL as [FAQ!3!Answer!element]

    UNION ALL
    select 2 as Tag,
    1 as Parent,
    '',
    rtrim(c.[category name]),
    NULL, NULL, NULL, NULL
    FROM faq_categories c

    UNION ALL
    SELECT 3 AS Tag,
    2 AS Parent,
    '',
    rtrim(c.[category Name]),
    rtrim(f.ix),
    f.timestamp,
    rtrim(f.question),
    rtrim(f.answer)

    FROM faq_categories c, faq f
    WHERE c.ix = f.category_ix
    ORDER BY [Category!2!CategoryName!element], [FAQ!3!Question!element]
    FOR XML EXPLICIT







    "keith chadwick" <> wrote in message
    news:...
    > Yes i found that stuff, had forgotten to install the some stuff i needed.
    >
    > I have a remaing problem. I have to get my web service to return the xml
    > returned from the SQL Server and I can not seem to get from the command
    > execute to the point of return 'something' in the web service?
    >
    > Any suggestions?
    > Keith
    >
    > "Irwin Dolobowsky [MSFT]" <> wrote in message
    > news:%...
    >> For doing straight FOR XML queries you'd probably want SqlCommand and the
    >> "ExecuteXmlReader" function. There are a lot of great articles up on MSDN
    >> on how XML integrates with ADO.Net.
    >>
    >> The example below is actually using an Xml Template to do the FOR XML
    >> query. For that you'd have to use the managed classes available as part
    >> of SqlXml. They are included in the latest release.
    >>
    >> --
    >> Thanks,
    >> Irwin
    >>
    >> Irwin Dolobowsky
    >> Program Manager, SqlXml
    >> http://blogs.msdn.com/irwando
    >>
    >> This posting is provided "AS IS" with no warranties, and confers no
    >> rights.
    >>
    >>
    >> "keith chadwick" <> wrote in message
    >> news:...
    >>> The system I am converting use a lot of sql that makes use of the for
    >>> xml clause provided for in sql server 2000. Below is an example of how
    >>> this is done in ASP:
    >>>
    >>> set dataxml=server.CreateObject("MSXML2.DOMDocument.4.0")
    >>> dataxml.setProperty "ServerHTTPRequest", false
    >>> dataxml.async=false
    >>>
    >>> dim sSQL
    >>> sSQL = "<?xml version=""1.0"" ?><ROOT
    >>> xmlns:sql=""urn:schemas-microsoft-com:xml-sql"">"
    >>> sSQL = sSQL & "<clients><sql:query>select * from client order by name
    >>> for xml auto</sql:query></clients>"
    >>> sSQL = sSQL & "<events><sql:query>select * from event order by name for
    >>> xml auto</sql:query></events>"
    >>> sSQL = sSQL & "</ROOT>"
    >>>
    >>> set cmd=server.CreateObject("ADODB.COMMAND")
    >>> cmd.ActiveConnection=application("DBCONNECTION")
    >>> cmd.CommandText=sSQL
    >>> cmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
    >>> cmd.Properties("Output Encoding")="ISO-8859-1"
    >>> cmd.Properties("XSL")="myxsltfile.xslt"
    >>> cmd.Properties("Output Stream")=dataxml
    >>> cmd.Execute ,,1024
    >>>
    >>> In the above example a transformation is being applied and the result
    >>> being placed inset the msxsml object for further processing.
    >>> In other cases we do not immediately apply a xsl and simply set the
    >>> Output Streeam to Response.
    >>>
    >>> How would I go about this in .NET with the same results?
    >>>
    >>> Thanks in Advance
    >>> Keith
    >>>
    >>>
    >>>
    >>>
    >>>

    >>
    >>

    >
    >
    Dino Chiesa [Microsoft], Oct 28, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?c2NvdHRybQ==?=

    sql session state for .net v1.1/.net v2.0 and sql server 2005

    =?Utf-8?B?c2NvdHRybQ==?=, Feb 13, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    2,777
    Steven Cheng[MSFT]
    Feb 16, 2006
  2. Harry Zoroc
    Replies:
    1
    Views:
    915
    Gregory Vaughan
    Jul 12, 2004
  3. news.sbcglobal.net
    Replies:
    2
    Views:
    631
    Erland Sommarskog
    Jun 11, 2006
  4. ecoolone
    Replies:
    0
    Views:
    741
    ecoolone
    Jan 3, 2008
  5. Erik Wasser
    Replies:
    5
    Views:
    428
    Peter J. Holzer
    Mar 5, 2006
Loading...

Share This Page