JSON to XML

[JTaylor]

Did we (or someone) ever figure out how to translate JSON to XML?  I've been going through what I have tried and what has been posted but not finding anything that handles JSON in any fashion.   Needing something that works for anyone who has the standard .NET stuff installed, if possible.  I've been looking at the following but it tells me the "DLL" is not available.   Would this be the right track?   Happy to work with the JSON if there is a standard solution for that but everyone seems to avoid the MS .NET stuff and go with Newtonsoft.    I know I can do some things with the MS Scripting stuff.

http://msdn.microsoft.com/en-us/library/system.runtime.serialization.json.jsonreaderwriterfactory%28v=vs.90%29.aspx

I've been attempting something along the lines of

ObjectClrOption("use","System.ServiceModel.Web, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")
oJson = ObjectClrNew('System.Runtime.Serialization.Json')
xml = oJson.XmlDictionaryReader(cTxt);

but not sure if this is correct....actually, I know it is not correct or it would work :-)

Suggestions?


Jim

[stanl]

Did we (or someone) ever figure out how to translate JSON to XML?

Jim;
Privately, I still use Powershell and it works great except for occasional datetype issues. However, PS uses the same ,NET classes you referenced so it sounds like an interesting project. I know Chuck was considering writing an Extender.

[stanl]

Jim;

maybe consider the JsonReaderWriterFactory.CreateJsonReader class, derived from the
  System.Runtime.Serialization.Json Namespace I came across this

Code Select Expand


public XmlDocument JsonToXML(string json)
    {
        XmlDocument doc = new XmlDocument();

        using (var reader = JsonReaderWriterFactory.CreateJsonReader(Encoding.UTF8.GetBytes(json), XmlDictionaryReaderQuotas.Max))
        {
            XElement xml = XElement.Load(reader);
            doc.LoadXml(xml.ToString());
        }

        return doc;
    }


Not sure about converting Encoding.UTF8.GetBytes(json), XmlDictionaryReaderQuotas.Max.

[JTaylor]

Yeah...that is what I was looking at....   Here is what I have so far.   I don't get any errors but I don't get any results either.  Guessing the GetBytes stuff isn't the right way to go or something along those lines.  Suggestions anyone?

Jim

Code Select Expand


;Thanks to Stan Littlefield for the WebRequest
;///////////////////////////////////////////////////////////////////////////////////////////////////////
If Version() < '2013A'
   Message("Note", "You Need WinBatch Version 2013A or Newer.")
   Exit
EndIf
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Inputs
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
url="http://openlibrary.org/api/books?bibkeys=ISBN:0451526538&jscmd=details"

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Load assembly into the WinBatch process
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ObjectClrOption("use","System.Net, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL")
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Create a class implemented by a managed assembly.
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Web = ObjectClrNew('System.Net.WebRequest')

Web1 = Web.Create(url)

oTxt = Web1.GetResponse()

cStream = oTxt.GetResponseStream()

oStream = ObjectClrNew( 'System.IO.StreamReader',cStream )

cTxt = oStream.ReadToEnd()
oStream.Close()
oTxt.Close()

Message("JSON",cTxt) ;returns as a variant

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; JSON Declaration
;
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ObjectClrOption("use","System.Runtime.Serialization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
oJson = ObjectClrNew('System.Runtime.Serialization.Json.JsonReaderWriterFactory')

xml_readerq = ObjectClrNew('System.Xml.XmlDictionaryReaderQuotas')

jsonb = ObjectClrNew('System.Text.Encoding')
jsonb = jsonb.UTF8.GetBytes(cTxt)

reader = oJson.CreateJsonReader(jsonb, xml_readerq)

cnt = reader.AttributeCount
txt = reader.ReadContentAsString()
message(cnt,txt)

oJson = 0
[code]

[JTaylor]

Okay...removed the other post as I got past the one problem but now getting "Non-Static Method Requires a Target" error on the xmlReader.Read(); line.  I sort of know what that means but can't seem to get past it.   First is my code and then the code I'm trying to convert.

Code Select Expand



ObjectClrOption("use","System.Runtime.Serialization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
oJson = ObjectClrNew('System.Runtime.Serialization.Json.JsonReaderWriterFactory')

xmlreaderq = ObjectClrNew('System.Xml.XmlDictionaryReaderQuotas')
xmldoc     = ObjectClrNew('System.Xml.XmlDocument')

symem = ObjectClrNew('System.IO.MemoryStream')
streamWriter  = ObjectClrNew('System.IO.StreamWriter',symem)


                streamWriter.Write(jtxt);
                streamWriter.Flush();
                symem.Position = 0;
                xmlReader = ObjectClrNew('System.Xml.XmlDictionaryReader',oJson.CreateJsonReader(symem,xmlreaderq))
                xmlReader.Read();
                xmldoc.LoadXml(xmlReader.ReadOuterXml());
                streamWriter.Close();


xtxt = xmldoc.Text
message("HEY",xtxt)


Code Select Expand


      public static XmlDocument ConvertJSONToXML(string JSONString)
        {
            XmlDocument document = new XmlDocument();
            using (MemoryStream memoryStream = new MemoryStream())
            {
                StreamWriter streamWriter = new StreamWriter(memoryStream);
                streamWriter.Write(JSONString);
                streamWriter.Flush();
                memoryStream.Position = 0;
                using (XmlDictionaryReader xmlReader = JsonReaderWriterFactory.CreateJsonReader(memoryStream, XmlDictionaryReaderQuotas.Max))
                {
                    xmlReader.Read();
                    document.LoadXml(xmlReader.ReadOuterXml());
                }
                streamWriter.Close();
            }
            return document;
        }
    }



[stanl]

I sort of know what that means but can't seem to get past it.

The docs seems to indicate that xmlReader.Read() reads in a node at a time, so probably have to set up some sore of While loop,  or maybe some sort of

Code Select Expand


Foreach node in xmlreader ;which I doubt can be done
   xmldoc.LoadXml(xmlReader.ReadOuterXml());
   xmlReader.Read();
Next


[JTaylor]

Don't see any way to just convert the whole document???    I'm a bit stumped at the moment.   Keep hoping the WinBatch folks will jump in.    I keep trying different things but no joy.  I'm not sure how I could create a node loop.  Guess if I could get to the nodes I probably wouldn't need to convert them :-)     Thought maybe one of my two approaches would work.  Was quite pleased I got as far as no errors on the first one...I think that is a first with this stuff.    Of course, I don't get any results but felt like progress to me ;-)

Thanks for the input.  I'll ponder it a bit more.

Jim

[stanl]

Again, according to the docs

Return Value Type: System.Boolean
true if the next node was read successfully; false if there are no more nodes to read.
So maybe a

Code Select Expand


While xmlReader.Read()
    xmldoc.LoadXml(xmlReader.ReadOuterXml())
EndWhile
   



   

[Deana]

Jim,

Looks like you are most of the way there. Not quite sure what is going on with the Read. I am having a similar issue with this code:

Code (winbatch) Select Expand


;***************************************************************************
;**   Convert JSON Stream to XML
;***************************************************************************
If Version( )< '2013A'
   Pause('Notice', 'Need 2013A or Newer Version of WinBatch')
   Exit
EndIf

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Inputs
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
url="http://openlibrary.org/api/books?bibkeys=ISBN:0451526538&jscmd=details"

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Load assembly into the WinBatch process
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ObjectClrOption("use","System.Net, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL")
ObjectClrOption("use","System.Runtime.Serialization, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089") ;3.0.0.0__b77a5c561934e089

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Get Json Stream
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Web = ObjectClrNew('System.Net.WebRequest')
Request = Web.Create(url)
oTxt = Request.GetResponse()
Stream = oTxt.GetResponseStream()

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Convert to XML
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

oJsonRWFactory = ObjectClrNew('System.Runtime.Serialization.Json.JsonReaderWriterFactory')
oXmlDictionaryReaderQuotas = ObjectClrNew('System.Xml.XmlDictionaryReaderQuotas')

;Creates An XmlDictionaryReader that can process JavaScript Object Notation (JSON) data.
Reader = oJsonRWFactory.CreateJsonReader( Stream, oXmlDictionaryReaderQuotas.Max )
Reader.MoveToContent()
arrResult =  ArrDimension(8)
While @True
   bRet = Reader.Read() ;!!!!!! 1298 ERROR !!!!!!
   if bRet == 0 then break
   BoxDataTag(1, "TAG1")
   arrResult[0] = Reader.Name
   arrResult[1] = Reader.LocalName
   arrResult[2] = Reader.NamespaceURI
   arrResult[3] = Reader.NodeType
   arrResult[4] = Reader.HasValue
   arrResult[5] = Reader.Value
   arrResult[6] = Reader.HasAttributes
   arrResult[7] = Reader.AttributeCount
   data = 'Name:          ':@TAB:arrRslt[0]:@LF:'LocalName:     ':@TAB:arrRslt[1]:@LF:'NamespaceURI:  ':@TAB:arrRslt[2]:@LF:'NodeType:      ':@TAB:arrRslt[3]:@LF:'HasValue:      ':@TAB:arrRslt[4]:@LF:'Value:         ':@TAB:arrRslt[5]:@LF:'HasAttributes: ':@TAB:arrRslt[6]:@LF:'AttributeCount:':@TAB:arrRslt[7]
   BoxDrawText(1, '0,0,1000,1000', data, @TRUE, 0)
   TimeDelay(0.5)
   BoxDataClear(1, "TAG1")   
EndWhile

EXIT

[Deana]

I am beginning to think the error is caused by invalid JSON data. I am going to look into removing the leading text 'var_OLBookinfo= and the ending semicolon.

Reference: http://jsonlint.com/

[JTaylor]

Yes.   I get that error if I try to use the Stream from the Web Request.   If I grab the response and write it to the memory stream then it tells me the Read requires a target.   If I give it a target it tells me that "Read" isn't found.

Appreciate the reply.   I will do some tweaking on your code since I've tried about everything I can think of on mine.


Stan, I get the same "target" message on the Read. 

Jim

[JTaylor]

Okay....I had tried that at some point but not sure it has been since I've gotten this far.

Jim

[JTaylor]

Doesn't make a difference in my code and not sure how to remove that in your code.

Jim

[Deana]

Great news. My theory was correct. if you pass valid json data the read will no longer fail.

Code (winbatch) Select Expand

;***************************************************************************
;**   Convert Json Data to XML
;**
;** Purpose: Convert Json Data to XML
;** Inputs: url that contains json data
;** Outputs: Results in XML file
;** Reference:
;**       REQUIRES WinBatch 2013A or newer
;**
;** Developer: Deana Falk 2013.08.07
;***************************************************************************
If Version( )< '2013A'
   Pause('Notice', 'Need 2013A or Newer Version of WinBatch')
   Exit
EndIf

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Inputs
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
url="http://openlibrary.org/api/books?bibkeys=ISBN:0451526538&jscmd=details"
outputfile = DirScript():"Output.XML"

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Load assembly into the WinBatch process
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ObjectClrOption("use","System.Net, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL")

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Get Json
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Web = ObjectClrNew('System.Net.WebRequest')
Web1 = Web.Create(url)
oTxt = Web1.GetResponse()
cStream = oTxt.GetResponseStream()
oStream = ObjectClrNew( 'System.IO.StreamReader',cStream )
cTxt = oStream.ReadToEnd()

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; JSON data returned from URL
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
;var _OLBookInfo = {"ISBN:0451526538": {"info_url": "http://openlibrary.org/books/OL1017798M/The_adventures_of_Tom_Sawyer", "bib_key": "ISBN:0451526538", "preview_url": "http://openlibrary.org/books/OL1017798M/The_adventures_of_Tom_Sawyer", "thumbnail_url": "http://covers.openlibrary.org/b/id/295577-S.jpg", "details": {"identifiers": {"goodreads": ["1929684"], "project_gutenberg": ["74"], "librarything": ["2236"]}, "subject_place": ["Mississippi River Valley", "Missouri"], "covers": [295577], "lc_classifications": ["PS1306 .A1 1997"], "latest_revision": 8, "genres": ["Fiction."], "source_records": ["marc:marc_records_scriblio_net/part25.dat:213801824:997", "marc:marc_loc_updates/v40.i23.records.utf8:2463307:1113"], "title": "The adventures of Tom Sawyer", "languages": [{"key": "/languages/eng"}], "subjects": ["Sawyer, Tom (Fictitious character) -- Fiction", "Runaway children -- Fiction", "Child witnesses -- Fiction", "Boys -- Fiction", "Mississippi River Valley -- Fiction", "Missouri -- Fiction"], "publish_country": "nyu", "by_statement": "Mark Twain ; with an introduction by Robert S. Tilton.", "oclc_numbers": ["36792831"], "type": {"key": "/type/edition"}, "revision": 8, "publishers": ["Signet Classic"], "last_modified": {"type": "/type/datetime", "value": "2012-06-13T23:35:32.882463"}, "key": "/books/OL1017798M", "authors": [{"name": "Mark Twain", "key": "/authors/OL18319A"}], "publish_places": ["New York"], "pagination": "xxi, 216 p. ;", "classifications": {}, "created": {"type": "/type/datetime", "value": "2008-04-01T03:28:50.625462"}, "lccn": ["96072233"], "notes": "Includes bibliographical references (p. 213-216).", "number_of_pages": 216, "dewey_decimal_class": ["813/.4"], "isbn_10": ["0451526538"], "publish_date": "1997", "works": [{"key": "/works/OL53919W"}]}, "preview": "noview"}};Start = StrIndex( cTxt, '{', 1, @Fwdscan )-1

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Parse Json Data
;  Remove leading variable 'var _OLBookInfo = ' and ending semi-colon
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
startptr = Strindex( cTxt, '{', 1, @Fwdscan )
endptr = StrLen(cTxt)-startptr
strJson = StrSub( cTxt, startptr, endptr )
Message("Revised JSON",strJson) ;returns as a variant

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
; Convert to XML
;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ObjectClrOption("use","System.Runtime.Serialization, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
ObjectClrOption("use","System.Xml.Linq, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
ObjectClrOption("use", "System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")

JsonRWFactory = ObjectClrNew('System.Runtime.Serialization.Json.JsonReaderWriterFactory')
XmlDictionaryReaderQuotas = ObjectClrNew('System.Xml.XmlDictionaryReaderQuotas')
Encoding = ObjectClrNew('System.Text.Encoding')
XElement = ObjectClrNew("System.Xml.Linq.XElement")
XmlDocument = ObjectClrNew("System.Xml.XmlDocument")

ByteArrayJson = Encoding.UTF8.GetBytes(strjson)

;Creates An XmlDictionaryReader that can process JavaScript Object Notation (JSON) data.
reader = JsonRWFactory.CreateJsonReader( ByteArrayJson, XmlDictionaryReaderQuotas.Max)

xml = XElement.Load(reader);
XmlDocument.LoadXml(xml.ToString());
XmlDocument.Save(outputfile)
Run(outputfile,"")

[JTaylor]

Thank you VERY much!!!    Glad to know I was heading in the right general direction for once  :)

Jim

[Deana]

See revised (simplified ) code that I just posted.

Reference: http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/dotNet/System_XML+Convert~Json~To~XML.txt

[JTaylor]

Most Excellent.   Interesting that Linq works.  It told me I couldn't use it earlier but guess something else was wrong that confused it.
  Thanks again.  This is exactly what I needed.

Jim

[stanl]

Most Excellent.

Yes. Nice script. But as a post-script... I tried your url against my Powershell script and it returned a blank XML header, as did running it against the changes Deana made.... and it failed there with a strange error that it encountered something bad at position 1843.

Converting xml to Json (assuming well-formed xml) has never been an issue, but the reverse seems to beg for specialized well-formatting on a per json basis.

Looks like MS has a new Javascript API for Office which might handle Json, but don't hold your breath.

[JTaylor]

Yeah...not sure why they tack on that variable at the beginning.   I tack on the <?xml version="1.0"?> to the xml it creates before I start working with it.   That site is actually not the one I am working with...it was just an open source to get similar data for testing.  The production one requires a key and it seems to be good so far.   Actually might have gotten things working if I had started with it as I wouldn't have hit those odd errors which made me think I was doing something wrong.   Kept getting COM Visible is set to false for things like Linq in addition to the other problems.   In any event, Deana saved the day once again and I think it is all solid for what I need apart from it occasionally telling me my key (for the webservice) is invalid and sometimes I get the "...target of invocation..." error but not sure if that is the .NET stuff or the webservice.

Jim