We help IT Professionals succeed at work.

XML Output to VB.Net from SQL Stored Procedure

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
SqlCmd.ExecuteNonQuery()
MsgBox(SqlCmd.Parameters("LastMsgID").Value)

My Stored Procedure is:

ALTER PROCEDURE  [dbo].[CreateD30XML]
varJobNo As nvarchar(20), @LastMsgID As nvarchar(max) Output

AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
    -- 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'),
            ROOT('Request')

END
Comment
Watch Question

Shahan AyyubSenior Software Engineer
CERTIFIED EXPERT

Commented:
Have you tried this instead:

Dim xmlData as string = SqlCmd.ExecuteScalar()

Open in new window

Shahan AyyubSenior Software Engineer
CERTIFIED EXPERT

Commented:
or make sure you have set this parameter:

@LastMsgID 

Open in new window

CERTIFIED EXPERT
Top Expert 2012

Commented:
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

Commented:
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()
xmlr.Read()
Do While xmlr.ReadState <> Xml.ReadState.EndOfFile
System.Diagnostics.Debug.WriteLine(xmlr.ReadOuterXml())
Loop
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.
Senior Software Engineer
CERTIFIED EXPERT
Commented:
        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!
Shahan AyyubSenior Software Engineer
CERTIFIED EXPERT

Commented:
Here few more solutions to this problem is discussed:
http://shahanayyub.wordpress.com/2012/09/09/321/