XML Output to VB.Net from SQL Stored Procedure

Posted on 2012-08-11
Last Modified: 2012-09-09
I'm trying to get the XML output from a stored procdure using  FOR XML PATH on my SQL2008 server for use in my VB.NET 2008 application. I can see the Stored procedure executes correctly from SQL Management Studio and VS 2008. I've tried many examples but can't get it to return any data. Please let me know what I'm doing wrong.

My current attempt is:

SqlCmd = New SqlCommand
SqlCmd.CommandText = "CreateD30XML" ' Stored Procedure to Call
SqlCmd.CommandType = CommandType.StoredProcedure
SqlCmd.Parameters.AddWithValue("varJobNo", "415974OC")
SqlCmd.Parameters.AddWithValue("LastMsgID", "")
SqlCmd.Parameters("LastMsgID").Direction = ParameterDirection.Output
SqlCmd.Connection = SQLCon

My Stored Procedure is:

varJobNo As nvarchar(20), @LastMsgID As nvarchar(max) Output

      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
    -- Insert statements for procedure here
      SELECT  'DeliveredOrderEntry' [@title],
        '441002' [@requestor],            
        HbNo AS [Data/DeliveredOrders/Order/@refnum],
        DLocNo AS [Data/DeliveredOrders/Order/marketname],
        Carrier AS [Data/DeliveredOrders/Order/carriername],
        DName AS [Data/DeliveredOrders/Order/customername],
        DAdr1 AS [Data/DeliveredOrders/Order/customeraddress],
        DCity AS [Data/DeliveredOrders/Order/customercity],
        DState AS [Data/DeliveredOrders/Order/customerstate],
        DZip AS [Data/DeliveredOrders/Order/customerzipcode],
        JETime AS [Data/DeliveredOrders/Order/deliverydate],
        Driver AS [Data/DeliveredOrders/Order/drivername],
        DriverNo AS [Data/DeliveredOrders/Order/driverid],
        JeTime AS [Data/DeliveredOrders/Order/arrivaltimedriver],
        JobNo AS [Data/DeliveredOrders/Order/shipmentid],
        'Delivery' AS [Data/DeliveredOrders/Order/shipmenttype],
        'Box' AS [Data/DeliveredOrders/Order/ordermode],
        'Delivery' AS [Data/DeliveredOrders/Order/ordertype],
        DPhone AS [Data/DeliveredOrders/Order/customerprimaryphonenumber],
        DPhone2 AS [Data/DeliveredOrders/Order/customersecondaryphonenumber],
        '' AS [Data/DeliveredOrders/Order/weekendingdate],
        BANo AS [Data/DeliveredOrders/Order/sellingstore],
        BANo AS [Data/DeliveredOrders/Order/fulfillingstore],
        BANo AS [Data/DeliveredOrders/Order/cfnumber],
        JEndType AS [Data/DeliveredOrders/Order/timepreference],
        '' AS [Data/DeliveredOrders/Order/timewindow]
FROM    JobLogTbl
WHERE   (JobNo = @varJobNo)
FOR     XML PATH('Transaction'),

Question by:GT84
    LVL 19

    Expert Comment

    by:Shahan Ayyub
    Have you tried this instead:

    Dim xmlData as string = SqlCmd.ExecuteScalar()

    Open in new window

    LVL 19

    Expert Comment

    by:Shahan Ayyub
    or make sure you have set this parameter:


    Open in new window

    LVL 75

    Expert Comment

    by:Anthony Perkins
    There seems to be some confusion here:

    You are attempting to return the result from FOR XML though an OUTPUT parameter, but have never assigned it to that parameter.  Further in my view although it can be done it is a mistake.

    Secondly ExecuteScalar returns the first column of the first row, but FOR XML does not return a resultset it returns a stream.

    What you should be doing is losing the OUTPUT parameter entirely and using the  ExecuteXmlReader method to retrieve the Xml.

    Author Comment

    I gave that a try but was unsuccessful. I ended up just creating the xml from a recordset and sending it to the Webservice as a string and it seems to be working . If you could give me a code example of how I might use the executeXmlReader in VB.NET, I would love to give it a try. I tried the following as a test couldn't get it to produce results. Maybe it was because I was using my FOR XML stored procedure and not a select query.

    Dim xmlr As System.Xml.XmlReader
    xmlr = SqlCmd.ExecuteXmlReader()
    Do While xmlr.ReadState <> Xml.ReadState.EndOfFile
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Maybe it was because I was using my FOR XML stored procedure and not a select query.
    No it is precisely because you are using FOR XML that requires you to use ExecuteXmlReader.
    LVL 19

    Accepted Solution

            Dim ds As New DataSet()
            ds.Tables.Add(SqlHandler.FillDataTable("Select ID, Name from tblDoctor for XML path"))
            Dim xDoc As New Xml.XmlDataDocument(ds)
            Dim xmlData As String = xDoc.InnerText

    Open in new window

    "xmlData" contains complete XML data that comes with Sql FOR XML path query.

    hope it helps!
    LVL 19

    Expert Comment

    by:Shahan Ayyub
    Here few more solutions to this problem is discussed:

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now