Highlighted
New Member.
5426 views

How to convert XML to JSON using VBSCRIPT?

Jump to solution

Hi All,

 

does anyone know how to convert an XML file to Json object.

I have a very complex XML which i am having hard time parsing. I have done JSON parsing earlier and feel much comfortable with that. 

If anyone knows of any function that I can use to convert my XML file to JSON that would be very much helpful.

 

Thanks in advance.

 

Tags (1)
0 Likes
1 Solution

Accepted Solutions
Highlighted
New Member.

Re: How to convert XML to JSON using VBSCRIPT?

Jump to solution

I was unable to figure out how to convert XML to JSON using VB SCript. So I build an API using JAVA that takes XML in the request and gives Json in the response. I used that API in my UFT script to convert XML to JSON. That was the best I could do.

View solution in original post

5 Replies
Highlighted
Absent Member.
Absent Member.

Re: How to convert XML to JSON using VBSCRIPT?

Jump to solution

To convert XML to JSON, you would need to parse the file anyway. Look for any utility tools on the internet that can do it for you. It will save you time and effort.


Accept as solution/Give Kudos to the author if the piece of information answered/helped you to solve your problem.
All the thoughts here are mine not related to my employer nor anyone.
0 Likes
Highlighted
New Member.

Re: How to convert XML to JSON using VBSCRIPT?

Jump to solution

 

Hi  swaroopjoshi, 
I found one function which converts XML to JSON but its working only if I have single element in my function.
If I have multi/repeating elemensts then its not working. I am not a VBscript expert so unable to undersatnd whats wrong in the function.
 
Example - 
For following XML its working and converting to JSON correctly - 
<?xml version="1.0" encoding="UTF-8" ?>
<Envelope>
<Header></Header>
<Body>
<getProductDataResponse>
<output>
<Long_Description>BLACK</Long_Description>
<Product_Type></Product_Type>
<Brand></Brand>
<Registrable>false</Registrable>
<UPC_List>
<Number>8672305300</Number>
<Primary>true</Primary>
<Color>BLACK</Color>
<Size>MEDIUM</Size>
<Store_List>
<Store_id>1</Store_id>
<Store_Sequence_number>9</Store_Sequence_number>
<StoreName>Paramus Furniture</StoreName>
</Store_List>
</UPC_List>
</output>
</getProductDataResponse>
</Body>
</Envelope>
 
 
{
  "Envelope": {
    "Body": {
      "getProductDataResponse": {
        "output": {
          "Long_Description": "BLACK",
          "Registrable": "false",
          "UPC_List": {
            "Number": "8672305300",
            "Primary": "true",
            "Color": "BLACK",
            "Size": "MEDIUM",
            "Store_List": {
              "Store_id": "1",
              "Store_Sequence_number": "9",
              "StoreName": "Paramus Furniture"
            }
          }
        }
      }
    }
  }
}

For following XML its not working - 
<?xml version="1.0" encoding="UTF-8" ?>
<Envelope>
<Header></Header>
<Body>
<getProductDataResponse>
<output>
<Long_Description>BLACK</Long_Description>
<Product_Type></Product_Type>
<Brand></Brand>
<Registrable>false</Registrable>
<UPC_List>
<Number>8672305300</Number>
<Primary>true</Primary>
<Color>BLACK</Color>
<Size>MEDIUM</Size>
<Store_List>
<Store_id>1</Store_id>
<Store_Sequence_number>9</Store_Sequence_number>
<StoreName>Furniture</StoreName>
</Store_List>
<Store_List>
<Store_id>2</Store_id>
<Store_Sequence_number>10</Store_Sequence_number>
<StoreName>Clothes</StoreName>
</Store_List>
<Store_List>
<Store_id>3</Store_id>
<Store_Sequence_number>11</Store_Sequence_number>
<StoreName>Paramus Rugs</StoreName>
</Store_List>
</UPC_List>
</output>
</getProductDataResponse>
</Body>
</Envelope>
 
 
{
  "Envelope": {
    "Body": {
      "getProductDataResponse": {
        "output": {
          "Long_Description": "BLACK",
          "Registrable": "false",
          "UPC_List": {
            "Number": "8672305300",
            "Primary": "true",
            "Color": "BLACK",
            "Size": "MEDIUM",
            "Store_List": {
              "Store_id": "1",
              "Store_Sequence_number": "9",
              "StoreName": "Paramus Furniture"
            }
          }
        }
      }
    }
  }
}
 
As you can see in above example its returning only one Store_List. The same with multi UPC_LIST and single Store_list then also it only gives one UPC_LIST and Store_List.
 
Following is the function - 
Function XML2JSON(ByVal xml)

'Create the COM object
Dim doc
Set doc = CreateObject("Msxml2.DOMDocument.3.0")

'Load the XML document and wait until it's ready
doc.async = False
doc.loadXML(xml)

'Get the root element of the document
Dim root
Set root = doc.documentElement

'Start calling the recursive conversion function at the root element
Dim outStr
outStr = XML2JSON_Recurse(root, False)
XML2JSON = outStr

End Function

Function XML2JSON_Recurse(ByVal node, ByVal isAnonymous)

nodeString = ""

'If the node has no children, then it contains the empty string
If Not node.hasChildNodes() Then

'Write a key-value pair unless this node is part of an array, in which case
'just write the empty string
If isAnonymous = False Then
nodeString = """" & node.baseName & """:"""""
Else
nodeString = """"""
End If

'If the node has only one child and no grandchildren, then its child is a leaf
'containing text
ElseIf node.firstChild Is node.lastChild And Not node.firstChild.hasChildNodes() Then

'Write a key-value pair unless this node is part of an array, in which case
'just write the text value
If isAnonymous = False Then
nodeString = """" & node.baseName & """:""" & node.text & """"
Else
nodeString = """" & node.text & """"
End If

Else

Dim isArray
isArray = False

'If this node's name ends in "_list", then it's an array rather than an
'object
If Right(node.baseName, 5) = "_list" Then
isArray = True
End If

'Recurse on each of the node's children, passing them the value of isArray
'as their isAnonymous parameter
For Each child In node.childNodes
nodeString = nodeString & XML2JSON_Recurse(child, isArray) & ","
Next

'Get rid of the final comma from the list of children
nodeString = Left(nodeString, Len(nodeString) - 1)

'Enclose the list in square or curly brackets (depending on isArray)
If isArray = True Then
nodeString = "[" & nodeString & "]"
Else
nodeString = "{" & nodeString & "}"
End If

'If this node is not an item in an array and is not the root node, prepend
'its name to create a key-value pair
If isAnonymous = False Then
If isArray = True Then
nodeString = """" & Left(node.baseName, Len(node.baseName) - 5) & """:" & nodeString
Else
If Not node.baseName = "root" Then
nodeString = """" & node.baseName & """:" & nodeString
End If
End If
End If

End If

XML2JSON_Recurse = nodeString

End Function
0 Likes
Highlighted
Absent Member.
Absent Member.

Re: How to convert XML to JSON using VBSCRIPT?

Jump to solution

Function looks ok. But you need to call the xml function recursively to parse through each node and pass the subsequent node in each recursive call, otherwise you will not be able to capture the nested tags.


Accept as solution/Give Kudos to the author if the piece of information answered/helped you to solve your problem.
All the thoughts here are mine not related to my employer nor anyone.
0 Likes
Highlighted
Absent Member.
Absent Member.

Re: How to convert XML to JSON using VBSCRIPT?

Jump to solution

Hi I am looking for the same thing! Did you figure it all out?

 

would you mind helping me?

 

thank you 🙂

0 Likes
Highlighted
New Member.

Re: How to convert XML to JSON using VBSCRIPT?

Jump to solution

I was unable to figure out how to convert XML to JSON using VB SCript. So I build an API using JAVA that takes XML in the request and gives Json in the response. I used that API in my UFT script to convert XML to JSON. That was the best I could do.

View solution in original post

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.