Creating a XML file from SQL data using VB.net

The project involved creating a xml file in the following format with the data from the SQLserver 2008 tables.

File Sample
<claimTrackingRequest>
<Target>
<identifier>1015100236</identifier>
<Type>RCV</Type>
<Comment>
<Code />
<Text>07/29 - comment test1 </Text>
</Comment>
</Target>
<Target>
<identifier>1015700095</identifier>
<Type>RCV</Type>
<Comment>
<Code />
<Text>07/25 - comment test1</Text>
</Comment>
<Comment>
<Code />
<Text>07/26 - comment test2 </Text>
</Comment>
<Comment>
<Code />
<Text>07/26 - comment test2 </Text>
</Comment>
</Target>
</claimTrackingRequest>

The above result was returned by the stored proc PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat
( The stored procedure details is listed at the end of the post)
I am using VB.NET to write the result returned by the stored proc to a file with extension XML
I am using a DATASET to pull the result and then using the STREAMWRITER to writer the dataset result to the file.

Sub Main()　
Dim sqlConn As New SqlConnection
sqlConn.ConnectionString = "Data Source=sqlsvr1;Initial Catalog=db01;Integrated Security=SSPI;"Dim sqlCmd As New SqlCommandsqlCmd.CommandText = "PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat"sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Connection = sqlConn
sqlCmd.CommandTimeout = 120
sqlCmd.Parameters.Add("@DTBEGIN", SqlDbType.Date).Value = "07/26/2011"sqlCmd.Parameters.Add("@DTEND", SqlDbType.Date).Value = "07/29/2011"Dim ds As New DataSetTrysqlConn.Open()
Dim a As System.Data.SqlClient.SqlDataAdaptera = New SqlDataAdapter(sqlCmd)
a.Fill(ds)
sqlConn.Close()

Using writer As StreamWriter = New StreamWriter("c:\test\notes.xml", True)
writer.Flush()
For Each _row As DataRow In ds.Tables(0).Rows
writer.Write(_row.Item(0).ToString())
Nextwriter.Close()
End Using
Catch ex As ExceptionEnd Try　
End Sub

 /*The stored proc returning the XML result is as follows */

--The proc is using XML Query

USE [db01]GO/****** Object: StoredProcedure [dbo].[PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat] Script Date: 08/03/2011 12:48:43 ******/SET
GO
SET
GO
CREATE ANSI_NULLS ON QUOTED_IDENTIFIER ON PROCEDURE [dbo].[PROC_NOTES_STATUSCHANGENOTES_BY_HOSCODE_SELECT_CNA_newformat] (@DTBEGIN SMALLDATETIME, @DTEND SMALLDATETIME)AS/*turn these off so that it wont error if it trys to divide by Zero when doing calcs*/SET
SETARITHABORT OFF ANSI_WARNINGS OFF/*Create a temp table*/CREATE TABLE #TEMPTBLCNA (
RECORD_NUM
C1 INT NULL, VARCHAR(1) NULL,COMMENT_CODE VARCHAR(100) NULL,STATUS_CODE INT NULL,STATUS_DESC VARCHAR(200) NULL,COMMENT VARCHAR(1500) NULL,ACCOUNT VARCHAR(12) NULL,HOSPITAL INT NULL,DATEENTERED DATETIME NULL,PTNO INT NULL,NOTEID INT IDENTITY(1,1) )/*GET 100's*/INSERT INTO #TEMPTBLCNA (RECORD_NUM, C1,COMMENT_CODE,STATUS_CODE,STATUS_DESC,COMMENT,ACCOUNT,HOSPITAL,DATEENTERED,PTNO)SELECTMIN(C.RECORD_NUM), LEFT(COMMENT_CODE,1),C.COMMENT_CODE,LEFT(C.COMMENT_CODE, 3),m.[STATUS],c.COMMENT,RIGHT(DBO.STRIP_NONNUMERIC_12(P.ACCOUNT), 12),p.HOSPITAL, CAST(CONVERT(VARCHAR(10),C.DATEENTERED,101) AS DATETIME),P.PTNOFROMCOMMENTS C INNER JOIN PATIENT_TABLE P ON C.PTNO = P.PTNOINNER JOIN MasterCodeList mON m.NewCode = LEFT(C.COMMENT_CODE, 3)
WHERE CAST(CONVERT(VARCHAR,C.DATEENTERED,101) AS DATETIME) BETWEEN '07/22/2011' AND '07/29/2011'
GROUP BY P.PTNO,P.ACCOUNT,C.Comment,C.COMMENT_CODE,p.HOSPITAL,C.DATEENTERED,LEFT(COMMENT_CODE,1),m.[status]ORDERBY P.PTNO--select * from #TEMPTBLCNASELECT
( c.ACCOUNT as 'identifier', 'RCV' as 'Type', SELECT '' AS 'Code',a.comment AS 'Text'
FROM #TEMPTBLCNA aWHERE a.ptno = c.ptnoFOR XML PATH(''), TYPE) AS 'Comment'
FROM #TEMPTBLCNA cgroup by c.ptno,c.ACCOUNTFOR XML PATH('Target'), ROOT('claimTrackingRequest')
DROP TABLE #TEMPTBLCNASET
SET ARITHABORT ON ANSI_WARNINGS ON