modify/beutify XML file or tags using vbscript?

Question

I have an XML file which is in below formate:

<payments/>
    <payment>
        <payment_type>
        </payment_type>
        <dataforpay>
        </dataforpay>
        <allocations/>
            <allocation>
                <id>
                </id>
                <notfind>
                </notfind>
                <amount>
                </amount>
            </allocation>
    </payment>

as its look very advance format, i want to update this format to below using vbscript.....please give suggestions?

<payments>
    <payment>
        <payment_type>
        </payment_type>
        <dataforpay>
        </dataforpay>
        <allocations>
            <allocation>
                <id>
                </id>
                <notfind>
                </notfind>
                <amount>
                </amount>
            </allocation>
        </allocations>
    </payment>
</payments>

Are you asking, for example to change <payments/><payment/> to <payments><payment/> ... </payments>? — oracle certified professional
– oracle certified professional, Commented Mar 17, 2015 at 11:31
Yes, but it is not every time. in one node its <payments/> this should be <payments>.........</payments> .. but <payment></payment> node is already in correct format. — Jaykishan Hirpara
– Jaykishan Hirpara, Commented Mar 17, 2015 at 12:32

Community · Accepted Answer · 2017-05-23 11:52:23Z

1

Try the code below. It finds self-closing tags with name in the plural, and followed by one or more pair of open-close tags with the same name in the singular, and it replaces each self-closing tag with open-close pair, and moves into it all following tags which was matched.

sCont = ReadTextFile("C:\Test\src.xml", -2)

With CreateObject("VBScript.RegExp")
    .Global = False
    .MultiLine = True
    .IgnoreCase = True
    Do
        ' pattern to match a self-closing tag with name in the plural followed by one or more pair of open-close tags with the same name in the singular
        .Pattern = "^[\r\n]*(\s*)<(\w+)s(\s+[^>]*)*/\s*>(\s*[\r\n]+)(\s*<\2(?:\s+[^>]*)*>[\s\S]*?</\2>)"
        If Not .Test(sCont) Then Exit Do
        ' replace matched self-closing tag with open-close pair, and moves first matched following tag into it 
        sCont = .Replace(sCont, "$1<$2s$3>$4$5$4$1</$2s>")
        ' pattern to match a pair of open-close tags with name in the plural containing one or more, and followed by one or more pair of open-close tags with the same name in the singular
        .Pattern = "((?:^\s*)<(\w+)s(?:\s+[^>]*)*>\s*[\r\n]+\s*<\2(?:\s+[^>]*)*>[\s\S]*?</\2>\s*[\r\n]+)(^\s*</\2s>\s*[\r\n]+)(\s*<\2(?:\s+[^>]*)*>[\s\S]*?</\2>\s*[\r\n]+)"
        Do While .Test(sCont)
            ' move matched tag with the same name in the singular into
            sCont = .Replace(sCont, "$1$4$3")
        Loop
    Loop
End With

WriteTextFile sCont, "C:\Test\dst.xml", -2

Function ReadTextFile(sPath, iFormat)
    With CreateObject("Scripting.FileSystemObject").OpenTextFile(sPath, 1, False, iFormat)
        ReadTextFile = ""
        If Not .AtEndOfStream Then ReadTextFile = .ReadAll
        .Close
    End With
End Function

Sub WriteTextFile(sCont, sPath, iFormat)
    With CreateObject("Scripting.FileSystemObject").OpenTextFile(sPath, 2, True, iFormat)
        .Write(sCont)
        .Close
    End With
End Sub

See XHTML parsing with RegExp disclaimer.

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Mar 17, 2015 at 19:16

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jaykishan Hirpara Over a year ago

Hi, How can we change/replace '&' with '&' for sCont. i am getting "Parse Error: A semi colon character was expected." when using this function.

omegastripes Over a year ago

You can do it without regex, use sCnt = Replace(sCnt, "&", "&")

oracle certified professional · Accepted Answer · 2015-03-17 13:31:12Z

The original format is not valid XML because it has 2 root nodes, so I've loaded the file using the standard Scripting.FileSystemObject to prevent any errors from XML parse errors

Option Explicit

dim fso: set fso = CreateObject("Scripting.FileSystemObject")
dim stream: set stream = fso.OpenTextFile("input.xml")
dim xml: xml = stream.ReadAll()
stream.close

To manipulate the XML, I load it into an MSXML2.DomDocument with a dummy root node so it's well-formed

dim xmldoc: set xmldoc = CreateObject("MSXML2.DomDocument")
xmldoc.setProperty "SelectionLanguage", "XPath"
xmldoc.async = false
if not xmldoc.loadXML("<root>" & xml & "</root>") then
    WScript.Echo xmldoc.parseError.reason
    WScript.Quit
end if

Then I use XPath to query the payments node (assuming only one) and the payment nodes (assuming more than one)

dim paymentsNode: set paymentsNode = xmldoc.selectSingleNode("//payments")
dim paymentNodes: set paymentNodes = xmldoc.selectNodes("//payment")

Then I iterate through each payment node and then querying the allocations node (assuming only one) and the allocation nodes (assuming more than one). Each allocation node is removed from its parent and added to the allocations node. The same is then done with the payment.

dim p
for p = 0 to paymentNodes.length - 1
    dim payment: set payment = paymentNodes.Item(p)
    dim allocationsNode: set allocationsNode = payment.selectSingleNode("./allocations")
    dim allocationNodes: set allocationNodes = payment.selectNodes("./allocation")

    dim a
    for a = 0 to allocationNodes.length - 1
        dim allocation: set allocation = allocationNodes.Item(a)
        allocation.parentNode.removeChild allocation
        allocationsNode.appendChild allocation
    next

    payment.parentNode.removeChild payment
    paymentsNode.appendChild payment
next

Because the payments node is now a valid root node, I reload the XML at the payment level into the xmldoc object to remove our temporary root node before saving to disk.

xmldoc.loadXML xmldoc.selectSingleNode("/root/payments").xml
xmldoc.save "output.xml"

An alternative to direct node manipulation would be to use an XSL Transform but again, you'd need to correct for the root node. This may be a better choice if your input XML file is large.

thanks for the help. Its good to know this. In my case I have large file with lots of node. from which some are in good formates and some are not. I have just give example for the payments node. i do have lots of different node which is same as payments node. I need to do this for all the node and for all the files? its may fail because some files are in correct format as well. So if is their any way like you suggest XSL Transform then please help me hear.

Collectives™ on Stack Overflow

modify/beutify XML file or tags using vbscript?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related