VB Script that can search for certain data "conent" in XML file

I pass the XML into this routine and it creates the CSV

Sub Q_28953013(parmXML)
    Dim oRE
    Dim oMatches
    Dim oM
    Dim oSM
    Dim oM_Matches
    Dim lngSM
    Dim oDic
    Dim vItem
    Dim CSVArray
    Dim oFS, oTS
    
    Const CSVColumns = "Name,Server,Database,User ID,Password,ProviderName,Integrated Security,Port,Data Source,Initial Catalog"
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Set oFS = CreateObject("scripting.filesystemobject")
    Set oTS = oFS.OpenTextFile("C:\users\mark\downloads\Q_28953013.txt", ForWriting, True, TristateFalse)
    oTS.writeline CSVColumns
    
    Set oDic = CreateObject("scripting.dictionary")
    oDic.comparemode = 1    'text compare
    
    lngSM = 0
    For Each vItem In Split(CSVColumns, ",")
        oDic(vItem) = lngSM
        lngSM = lngSM + 1
    Next
    
    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.ignorecase = True
    oRE.Pattern = "(name)=""([^""]*)"" connectionString=""([^""]+)"" +(providerName)=""([^""]*)"""

    If oRE.test(parmXML) Then
        Set oMatches = oRE.Execute(parmXML)
        oRE.Pattern = "(Server|User ID|Database|User ID|Integrated Security|Initial Catalog|Data Source|Password|Port)=([^;""]*)"
        For Each oM In oMatches
            ReDim CSVArray(oDic.Count - 1)
            With oM
                CSVArray(oDic(oM.submatches(0))) = oM.submatches(1)
                Set oM_Matches = oRE.Execute(oM.submatches(2))
                For Each oSM In oM_Matches
                    CSVArray(oDic(oSM.submatches(0))) = oSM.submatches(1)
                Next
                CSVArray(oDic(oM.submatches(3))) = oM.submatches(4)
            End With
            oTS.writeline Join(CSVArray, ",")
        Next
    End If
    
    oTS.Close

End Sub

Select allOpen in new window

What i should put in this location?
Set oTS = oFS.OpenTextFile("C:\users\mark\downloads\Q_28953013.txt", ForWriting, True, TristateFalse)


Where I can find result-output file?

How I should apply this script or where?

That is the path for the output file.  The sub should be part of the script that downloads or opens the XML file.

Would you be able to provide me missing part of the script that opens XML file

Why?  You are developing a VBScript solution for some purpose.  I just helped you by posting a solution to your parsing question.  What have you already tried?

But, when i ran against xml file. I am not getting any output. CSV file is not created.

what does your code look like?

I don't have any code. I was looking for help to create one.
what i have is only XML file that contain data that i need to export to csv file

i ran against xml file

How could you have run it against a file when you don't have any other VBScript code?

Like i said in description.  So, what is the script you provide

 "I am looking for a VB script that would run against core.xml file and it would look for content  between  "<connectionStrings>"
Please see Core.txt attachment

Search Content:  Name  |  server  |  Database  |  User  ID | Password | ProviderName | Integrated Security | Data Source | Initial Catalog | Port

Entire search result should be exported into CSV file, attached.output.csv

Option Explicit

    Dim oFS, oTS
    Dim strXML
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Set oFS = CreateObject("scripting.filesystemobject")
    Set oTS = oFS.OpenTextFile("C:\users\mark\downloads\Core.txt", ForReading, True, TristateFalse)
    strXML = oTS.readall
    oTS.Close
    Q_28953013 strXML

Sub Q_28953013(parmXML)
    Dim oRE
    Dim oMatches
    Dim oM
    Dim oSM
    Dim oM_Matches
    Dim lngSM
    Dim oDic
    Dim vItem
    Dim CSVArray
    Dim oFS, oTS
    
    Const CSVColumns = "Name,Server,Database,User ID,Password,ProviderName,Integrated Security,Port,Data Source,Initial Catalog"
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Set oFS = CreateObject("scripting.filesystemobject")
    Set oTS = oFS.OpenTextFile("C:\users\mark\downloads\Q_28953013.txt", ForWriting, True, TristateFalse)
    oTS.writeline CSVColumns
    
    Set oDic = CreateObject("scripting.dictionary")
    oDic.comparemode = 1    'text compare
    
    lngSM = 0
    For Each vItem In Split(CSVColumns, ",")
        oDic(vItem) = lngSM
        lngSM = lngSM + 1
    Next
    
    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.ignorecase = True
    oRE.Pattern = "(name)=""([^""]*)"" connectionString=""([^""]+)"" +(providerName)=""([^""]*)"""

    If oRE.test(parmXML) Then
        Set oMatches = oRE.Execute(parmXML)
        oRE.Pattern = "(Server|User ID|Database|User ID|Integrated Security|Initial Catalog|Data Source|Password|Port)=([^;""]*)"
        For Each oM In oMatches
            ReDim CSVArray(oDic.Count - 1)
            With oM
                CSVArray(oDic(oM.submatches(0))) = oM.submatches(1)
                CSVArray(oDic(oM.submatches(3))) = oM.submatches(4)
                Set oM_Matches = oRE.Execute(oM.submatches(2))
                For Each oSM In oM_Matches
                    CSVArray(oDic(oSM.submatches(0))) = oSM.submatches(1)
                Next
            End With
            oTS.writeline Join(CSVArray, ",")
        Next
    End If
    
    oTS.Close

End Sub

Select allOpen in new window

This will load your xml file and output the csv:

Set xmlDoc = CreateObject("Microsoft.XMLDOM")

xmlDoc.Async = "False"
xmlDoc.Load("Core.xml")
if (xmlDoc.parseError.errorCode <> 0) then
   set myErr = xmlDoc.parseError
   WScript.Echo("You have an error: " + myErr.reason)
else 
	Set objRoot = xmlDoc.documentElement
	xmlDoc.setProperty "SelectionLanguage", "XPath" 
	Set colNodes = xmlDoc.selectNodes("//connectionStrings/add")
	
	DeleteFile "output.csv"
	WriteLineToFile "Name,server,Database,User ID,Password,ProviderName,Integrated Security,Port,Data Source,Initial Catalog"
	
	For Each objNode in colNodes
		Set row = CreateObject("Scripting.Dictionary")
		
		row.add "name",""
		row.add "Server",""
		row.add "Database",""
		row.add "User ID",""
		row.add "Password",""
		row.add "providerName",""
		row.add "Integrated Security",""
		row.add "Port",""
		row.add "Data Source",""
		row.add "Initial Catalog",""
		
		For Each a in objNode.attributes
			select case (a.name)
				case "name"
		   			row.Item("name") = a.nodeValue
		   		case "connectionString"
		   			csparts = Split(a.nodeValue,";")
		   			For Each c in csparts
		   				vars = Split(c,"=")
		   				row.Item(vars(0)) = vars(1)
		   			Next
		   		case "providerName"
		   			row.Item("provider") = a.nodeValue
		   	end select
		Next
		'write line
		WriteLineToFile row.Item("name") & "," & _
			row.Item("Server") & "," & _
			row.Item("Database") & "," & _
			row.Item("User ID") & "," & _
			row.Item("Password") & "," & _
			row.Item("providerName") & "," & _
			row.Item("Integrated Security") & "," & _
			row.Item("Port") & "," & _
			row.Item("Data Source") & "," & _
			row.Item("Initial Catalog")
	Next
end if

Sub WriteLineToFile(r)
   Const ForReading = 1, ForWriting = 2, ForAppending = 8
   Dim fso, f
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set f = fso.OpenTextFile("output.csv", ForAppending, True)
   f.WriteLine r
End Sub

Sub DeleteFile(f) 
	On Error Resume Next
	Dim fso
	Set fso = CreateObject("Scripting.FileSystemObject")
	fso.DeleteFile f, true
	On Error Goto 0
End Sub

Select allOpen in new window

Hi Rob

Your script is almost perfect.

There is one thing that is still a little bit off. When the script is looking for object "user id" it will only look for "user id" small letters. But,  in the core.xml I have couple  "User ID" with capital letter,
Script skips names if the User ID is capitalize  (User ID=Corer)

Can you fix this so the script would look for both ( user id and User ID) and add them to column under User ID in csv file?


Thx, M

<add name="monge" connectionString="Server=thc-WPS;Database=DervCA;User ID=Corer;Password=xxxxxx" providerName="System.Data.SqlClient"/>
 <add name="rem" connectionString="Server=PT-WP;Database=ASCo.System;user id=zgt;Password=xyyyy" providerName="System.Data.SqlClient"/>

I found one more thing about user id issues: in the core file I have also UID.

Can you modify script so it will look  for all (user id, User ID and UID)  add export results  to column under User ID in csv file?

I ran couple test against another core.xml file and I am getting an error.
What is causing this ?
core.txt

Error:  you have an error: End tag was not expected at this location.

That last error is due to malformed xml. Not much you can do about that unless you're editing the xml before running the script?

I'll add the user ID stuff for you

If you look at your xml file, the first three lines:

<?xml version="1.0"?>
 </assemblySearchPaths>    <=================== you can't have this stray end tag
        <connectionStrings>

Thank you so  much. This script works perfect.
Very professional help.