Convert MarkLogic XML to JSON
I am trying to convert this XML file to JSON but could not succeed. I have two child elements in my XML, but it only returns the last one. How do I get both records in JSON format?
XML
<Carousel>
<Video>
<Title>1</Title>
<Abstract>3</Abstract>
<FileName type="custom" mediatype="image">D</FileName>
<HasAccess>4</HasAccess>
</Video>
<Video>
<Title>1</Title>
<Abstract>2</Abstract>
<FileName type="custom" mediatype="image">D</FileName>
<HasAccess>3</HasAccess>
</Video>
</Carousel>
XQUERY:
import module namespace json = "http://marklogic.com/xdmp/json" at "/MarkLogic/json/json.xqy";
let $custom := let $config := json:config("custom")
return
(
map:put( $config, "whitespace", "ignore" ),
$config
)
let $XML := $XMLFile (: XML content :)
return json:transform-to-json($XML,$custom)
Current OUTPUT:
{"Carousel":{"Video":{"Title":"1", "Abstract":"2", "FileName":{"type":"custom", "mediatype":"image", "_value":"D"}, "HasAccess":"3"}}}
I also tried it with the default "Basic" JSON setting json:transform-to-json($XML)
, but got an error like
XML Element not in expected namespace [http://marklogic.com/xdmp/json/basic] (json:INVALIDELEM): Carousel
It only works in "Full" mode, but adds additional JSON information like _attribute, etc., which is not required in my JSON release.
Please help me or make it clear why it doesn't return full JSON output.
source to share
You will need to mark the duplicate items as array items for starters. This seems to work:
xquery version "1.0-ml";
import module namespace json = "http://marklogic.com/xdmp/json" at "/MarkLogic/json/json.xqy";
let $xml := <Carousel>
<Video>
<Title>1</Title>
<Abstract>3</Abstract>
<FileName type="custom" mediatype="image">D</FileName>
<HasAccess>4</HasAccess>
</Video>
<Video>
<Title>1</Title>
<Abstract>2</Abstract>
<FileName type="custom" mediatype="image">D</FileName>
<HasAccess>3</HasAccess>
</Video>
</Carousel>
let $custom :=
let $config := json:config("custom")
let $_ := map:put( $config, "whitespace", "ignore" )
let $_ := map:put( $config, "array-element-names", "Video" )
return $config
return json:transform-to-json($xml,$custom)
You can probably suppress attributes as well if you like. The configuration supports all sorts of options, I recommend looking at its documentation:
http://docs.marklogic.com/json:config
NTN!
source to share
The docs are here http://docs.marklogic.com/json/json
Moving on to the concept, but it is often overlooked that there is a fundamental difference in strategies, they are designed for different use cases.
The default ("basic") strategy is designed specifically to accept arbitrary JSON and convert it to and from XML blackbox with 100% accuracy.
The "full" strategy degenerates to the opposite, to get arbitrary XML and convert to and from black box JSON to resonant precision (some XML functionality gets lost in the process).
None of these are intended to control (or bother) the target format, and they do not intend (or do not work) with any changes to the target format.
If you need some control over the source and target formats, the "custom" strategy is for controlling the conversion in a "normal" way. As a result, you have much more control over the JSON and XML formats, on the other hand, the simplifications required to get "good" results in one format or the other require schema or data assumptions that are not always correct. Configuration options help control this, but ultimately, due to a mismatch between XML and JSON data models, it is generally impossible to create a transform that is ideal for Document A and Document B without making the configuration so complex that you would be better off manually coding it. So consider a "normal" conversion - a trade-off,designed to suit a large set of common patterns, but might not be exactly what you want. You can expand the range, be it a little more flexible in your requirements (XML or JSON formats), or by adding a pre or post processng step to convert your data to a format that is closer to what the custom format can handle.
source to share