How do I save stored procedure to XML file on server?

[Mark B]

I have a stored procedure that outputs an XML file.

How do I get ASP.net to save that XML as a file?

I have tried the following but it doesn't like the
cmd.ExecuteReader().ToString.


sqlConnection1.Open()
Dim doc As New XmlDocument()
doc.LoadXml(cmd.ExecuteReader().ToString)
doc.Save(filename)

 

[Hans Kesting]

Mark B formulated the question :

I have a stored procedure that outputs an XML file.

How do I get ASP.net to save that XML as a file?

I have tried the following but it doesn't like the
cmd.ExecuteReader().ToString.


sqlConnection1.Open()
Dim doc As New XmlDocument()
doc.LoadXml(cmd.ExecuteReader().ToString)
doc.Save(filename)

The ExecuteReader opens a DataReader: you will have to loop through the
rows and then through the columns yourself.

If the command returns just that XML document from the database, maybe
you want to use ExecuteScalar. This returns the first column of the
first row (as a plain 'object').

Hans Kesting

 

[Mark B]

This is sort of working in that ExecuteScalar is returning the data, but I
am getting the error message:

System.Xml.XmlException: Invalid XML document, The document does not have a
root element..
at System.Xml.XmlDocument.Save(String filename)

The function:

Public Function LanguageValuesGet( _
ByVal strPlatform As String, _
) As String

'Output stored procedure result as XML
Dim sqlConnection1 As New
SqlConnection(sfGeneral.fGetConnectionString())
Dim cmd As New SqlCommand
Dim strLanguageCode As String = ""

Dim ds As New DataSet
cmd.Connection = sqlConnection1
cmd.CommandText = "uspServicesLanguageValuesGet"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@EnterPlatform", strPlatform)
Dim filename As String = (Server.MapPath("~/language/" + strPlatform
+ "/language_values.xml"))

Dim doc As New XmlDocument()

Try
sqlConnection1.Open()
doc.LoadXml(cmd.ExecuteScalar())

Finally
doc.Save(filename)
sqlConnection1.Close()
sqlConnection1.Dispose()
cmd.Dispose()
End Try

ds.Dispose()
End Function


The Stored Procedure:

ALTER PROCEDURE [dbo].[uspServicesLanguageValuesGet]

@EnterPlatform varchar(20)

AS
BEGIN

SELECT LanguageCode AS [*], (
SELECT LookupID AS [*], LanguageText
FROM tblLanguageValues b
WHERE (a.LanguageCode=b.LanguageCode) and (Platform = @EnterPlatform)
FOR XML PATH('LookupID'), TYPE) AS [*]
FROM (SELECT DISTINCT LanguageCode FROM tblLanguageValues) a
FOR XML PATH('LanguageCode'), ROOT('XMLData')


END;


The data returned:

<XMLData>
<LanguageCode>AR-AE<LookupID>254<LanguageText>أض٠ÙÙŠ
الاختبار</LanguageText></LookupID><LookupID>330<LanguageText>أض٠للتجارب ÙÙŠ
اللغة 1</LanguageText></LookupID><LookupID>388<LanguageText>...




The table:

CREATE TABLE [dbo].[tblLanguageValues](
[AutoNumberID] [int] IDENTITY(9198,1) NOT NULL,
[LanguageCode] [varchar](20) NOT NULL CONSTRAINT
[DF_tblLanguageValues_LanguageCode] DEFAULT ('EN-US'),
[LookupID] [int] NOT NULL,
[LanguageText] [nvarchar](4000) NOT NULL,
[ToDo] [bit] NULL,
[LastUpdatedBy] [nvarchar](200) NULL,
[LastUpdatedDateTime] [datetime] NULL CONSTRAINT
[DF_tblLanguageValues_LastUpdatedDateTime] DEFAULT (getdate()),
[Platform] [nvarchar](100) NULL CONSTRAINT [DF_tblLanguageValues_Platform]
DEFAULT (N'www'),
CONSTRAINT [PK_tblLanguageValues] PRIMARY KEY CLUSTERED
(
[LanguageCode] ASC,
[LookupID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]