1 Dim NewFile   
2 Dim OldFile   
3 Dim JudgesFile   
4 Dim strSQL   
5 Dim rsDoc   
6 Dim XMLString   
7 Dim fso1    
8 Dim fso2    
9 Dim fso3   
10 Const ForWriting = 2   
11 Const ForReading = 1   
12 Dim JudgeFileLine   
13 Dim OldFileLine   
14 Dim dbconnect   
15   
16 'Create an instance of the File System Object for each file needed   
17 Set fso1 = CreateObject ("Scripting.FileSystemObject")   
18 Set fso2 = CreateObject ("Scripting.FileSystemObject")   
19 Set fso3 = CreateObject ("Scripting.FileSystemObject")   
20   
21 'Connect to the Database   
22 Set dbconnect = CreateObject("ADODB.Connection")   
23 dbconnect.Open "DRIVER={SQL Server}; SERVER=localhost; UID=sa; PWD=password; Database=supreme"  
24   
25 'Create Recordset   
26 Set rsDoc = CreateObject("ADODB.Recordset")   
27   
28 'Query the database to gather the presiding judge information, results are shown as xml   
29 strSQL = "SELECT JudicialOfficerID" + "'" + "@id" + "'" + ", isnull(NamePrefix," + "'" + "'" + ") as prefix, isnull(FirstName," + "'" + "'" + ") as firstName, isnull(MiddleName," + "'" + "'" + ") as middleName, isnull(LastName," + "'" + "'" + ") as lastName, isnull(NameSuffix," + "'" + "'" + ") as suffix FROM JudicialOfficer LEFT JOIN JudicialStatus ON JudicialOfficer.JudicialStatusID = JudicialStatus.JudicialStatusID WHERE JudicialType=" + "'" + "Judge" + "'" + "and Title is not null AND FirstName not like" + "'" + "%Judicial District Cour%" + "'" + "AND (JudicialStatus.JudicialStatus Like" + "'"+ "Active" + "'" + ") ORDER BY FullName1 FOR XML path(" + "'"+ "judge" + "'" + "), root(" + "'" + "judges" + "'" + ")"  
30   
31 rsDoc.Open strSQL, dbconnect    
32   
33 'Create a xml file for the query results   
34 Set JudgesFile = fso1.OpenTextFile("C:\CMS Application Files\Efiling XML\Judges.xml", ForWriting, True)   
35   
36 'Write the recordset data into the judges xml file   
37 Do Until rsDoc.EOF   
38     XMLString = Replace(CStr(rsDoc.Fields(0)), "><", ">" & vbCrLf & "<")   
39     JudgesFile.Write XMLString   
40     rsDoc.MoveNext   
41 Loop  
42   
43 JudgesFile.close   
44   
45 Set rsDoc = Nothing  
46 If Not dbConnect is Nothing then   
47     dbConnect.Close   
48 End if   
49 Set dbConnect = Nothing  
50   
51 'Create a new XML file for writing, Open the old XML file for reading, Open the judges file for reading   
52 Set JudgesFile = fso1.OpenTextFile("C:\CMS Application Files\Efiling XML\Judges.xml", ForReading, False)      
53 Set NewFile = fso2.OpenTextFile("C:\CMS Application Files\Efiling XML\test.xml", ForWriting, True)   
54 fso3.CopyFile "\\EflexTest\c$\jboss-3.2.3\server\default\conf\DistrictCourtFile.xml","\\cmstest\c$\CMS Application Files\Efiling\DistrictCourtFile.xml", True  
55 Set OldFile = fso3.OpenTextFile("\\cmstest\c$\CMS Application Files\Efiling\DistrictCourtFile.xml", ForReading, False)   
56   
57 'Read the old XML file and write each line to the new xml file until reaching the judges element   
58 Do Until OldFile.AtEndOfStream   
59     OldFileLine = OldFile.ReadLine   
60     If OldFileLine = "  <judges>" or OldFileLine = "<judges>" then Exit Do  
61     Else  
62         NewFile.WriteLine OldFileLine   
63     End If    
64 Loop  
65   
66 'Add in the new judges element   
67 Do Until JudgesFile.AtEndOfStream   
68     JudgeFileLine = JudgesFile.ReadLine   
69     NewFile.WriteLine  JudgeFileLine   
70 Loop  
71   
72 'Read the old xml file after the judges element until the end of the file and write each line to the new xml file   
73 Do until OldFile.AtEndOfStream   
74     OldFileLine = OldFile.ReadLine   
75     If OldFileLine = "  </judges>" or OldFileLine = "</judges>" then   
76         Do until OldFile.AtEndOfStream   
77             OldFileLine = OldFile.ReadLine   
78             NewFile.WriteLine OldFileLine   
79         Loop and Exit Do  
80     End If  
81 Loop  
82   
83 NewFile.close   
84 OldFile.close   
85 JudgesFile.close