StrataFrame Forum » StrataFrame Application Framework - V1 » WebForms (How do I?) » XML Problem while using Stored Procedure

XML Problem while using Stored Procedure

Doron Farber — Mon, 22 Jun 2009 07:00:20 GMT

Hi Trent,

I am actually running an sql statement and getting the XML via the ms sql 2008. Testing the XML within the ms sql 2008, it is just fine. When running the below code the XML string is broken. I am using the BO which is the parent of all the tables that are included within the sql statement? When I copy this line Me.ExecuteScalar(cmd) into the Watch window and look into the XML string I am getting an error.

How can I resolve this problem?

Public Function GetXMLMenuOptions() As String
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "XML_MenuOptions"

Return Me.ExecuteScalar(cmd)

End Function

Thanks,

Doron

RE: XML Problem while using Stored Procedure

Trent L. Taylor — Mon, 22 Jun 2009 07:00:20 GMT

Possibly. To be honest, I don't understand why you can't bring back more than one value at a time. Whenever I do something like this I want to take as few trips to the server as possible...this will also make the code less complicated.

So before I ever put ANYTHING into a BO, I make sure that I get my data lined out. This generally means that I will open up SQL Server Management Studio and play with the query which will ultimately become a SPROC to retrieve all data sets as I want them to be exported. Now sometimes this isn't totally possible as some massaging may need to take place once in a BO. However, I will be far closer to my goal than otherwise.

The point I was trying to make is that it seems that you could create all of your result sets and populate all of the BOs in one trip versus getting one piece of data at a time.

RE: XML Problem while using Stored Procedure

Doron Farber — Fri, 19 Jun 2009 14:41:47 GMT

Hi Trent,

Thanks for your reply.

[quote][b]Trent L. Taylor (06/19/2009)[/b][hr]You would be better served to retrieve all necessary result sets and values in a single query versus calling scalar methods if you are going to have very many queries. But to my knowledge there is no limitation.[/quote]

You mean instead of using this code: Me.ExecuteScalar(cmd)

I should use the below:
Me.FillDataTable(cmd)
MyBO.CurrentDataTable.WriteXml("c:\OutFile.XML", XmlWriteMode.WriteSchema)

That means since I have fields from other tables I will need to add those properties in that BO as well
Is that what you meant?

Thanks,

Doron

RE: XML Problem while using Stored Procedure

Trent L. Taylor — Fri, 19 Jun 2009 08:43:01 GMT

To my knowledge there is not a character limit on a scalar value. The only limitation is that a single value is returned. I am curious as to why you don't return more than a single value at once anyway. You would be better served to retrieve all necessary result sets and values in a single query versus calling scalar methods if you are going to have very many queries. But to my knowledge there is no limitation.

RE: XML Problem while using Stored Procedure

Doron Farber — Wed, 17 Jun 2009 12:21:34 GMT

Hi Trent,

Thanks for your reply, let me explain this better...

1)
I have a stored procedure named XML_MenuOptions which returns the XML file with the correct format. The fields Tip and Comment are NVARCHAR type and contains RTF data from an SF RTF control. Here is the SP.

SELECT
dbo.ServiceOptions.ServiceOption,
dbo.ServiceOptions.DisplayOptionOrder,
dbo.SearchOptions.SearchOption ,
dbo.SearchOptions.DisplayOptionOrder,
dbo.SearchCategories.SearchCategory,
dbo.SearchCategories.DisplayOptionOrder,
dbo.SearchCategories.Comment,
dbo.SearchCategories.Tip
FROM
dbo.ServiceOptions
  INNER JOIN dbo.SearchOptions
ON dbo.ServiceOptions.ServiceOptions_PK=dbo.SearchOptions.ServiceOptions_FK
  INNER JOIN dbo.SearchCategories
ON dbo.SearchOptions.SearchOptions_PK = dbo.SearchCategories.SearchOptions_FK
ORDER BY dbo.ServiceOptions.DisplayOptionOrder,dbo.SearchOptions.DisplayOptionOrder,dbo.SearchCategories.DisplayOptionOrder
FOR XML AUTO , ROOT('services')

2)
I created an Scalar function in a BO (see code below) to execute the SP and returns the XML file from as string. I noticed the following when executing the scalar function:
a) If the RTF fields "Tip and Comment" are included, then the string returned is not completed rendering the XML file invalid.

[codesnippet]Public Function GetXMLMenuOptions() As String
Dim cmd As New SqlCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "dbo.XML_MenuOptions"
Return CType(Me.ExecuteScalar(cmd), String)
End Function[/codesnippet]

b) If the RTF fields are excluded, then the string returned is completed and the XML file is OK.

c) If the RTF fields are included, and I limit the records to show using a WHERE condition, then the string returned is completed and the XML file is OK.

At this point, I could say that there should be a problem with the amount of characters that can be returned by the Scalar function. Do you have any suggestion on this regard? :hehe:

Thanks,

Doron

RE: XML Problem while using Stored Procedure

Edhy Rijo — Wed, 17 Jun 2009 07:34:37 GMT

Thanks will try it out.

RE: XML Problem while using Stored Procedure

Trent L. Taylor — Wed, 17 Jun 2009 07:31:27 GMT

You have to include the schema when writing the XML file. That should be all that is required. Here is the code that produces that XML file:

[codesnippet]loMessages.CurrentDataTable.WriteXml(lcPath & "Messages_" & lnCodePage.ToString() & ".XML", XmlWriteMode.WriteSchema)[/codesnippet]

RE: XML Problem while using Stored Procedure

Edhy Rijo — Wed, 17 Jun 2009 07:26:53 GMT

Hi Trent,

We are still having a problem here with this approach. There are 2 fields nVarchar which are used to hold RTF data, pretty much like in the SF Messaging tool. The problem is that if these 2 fields are included in the SQL which will return the XML file, when using the BO.ExecuteEscalar() the string returned seems to be incomplete. My guess is that we will need to apply some sort of conversion to that string to be able to properly get the RTF data in the file.

Since you are using something similar in the SF Mesaging application, is there something that needs to be done to get a proper XML file with RTF data in it?

RE: XML Problem while using Stored Procedure

Trent L. Taylor — Wed, 17 Jun 2009 07:12:50 GMT

Glad you got it going! :)

RE: XML Problem while using Stored Procedure

Doron Farber — Tue, 16 Jun 2009 20:05:51 GMT

Hi Trent,

After looking at the code I made it work, since I removed 2 nVARCHAR(MAX) columns from the SQL statement where it is used as an RTF. Is there any way to make it work with the nVARCHAR(MAX) data type, or any other field type that will allow me to work with RTF.

Thanks,

Doron