Build a pureXML and JSON application, Part 1: Store and query JSON with DB2 pureXML

Adopt a simple JSON-to-XML mapping

JavaScript Object Notation (JSON), a popular textual notation in Web 2.0, is used to represent objects (or data structures) as serialized text when clients and servers exchange information. Some applications benefit from persisting JSON objects to maintain state across sessions. In this article, learn how DB2® pureXML® can store, manage, and query JSON when you adopt a simple JSON-to-XML mapping.

Share:

Nuno Job, CoOp: DB2 Technical Enablement Specialist, IBM  

Photo of Nuno JobNuno Job is a masters student from University of Minho who did an internship in IBM at the T.J. Watson Research Center on the pureXML enablement team. Amongst his interests you can find Open-Source technologies, Linux, Security, Privacy, Web and Hierarchical Databases. Currently Nuno is working in IBM in Toronto where he works as a DB2 Technical Enablement Specialist, helping customers getting validated on IBM partner programs and maintaining an IBM Ruby on Rails application.



Susan Malaika, Senior Technical Staff Member, IBM

Photo of Susan MalaikaSusan Malaika is a Senior Technical Staff Member in the IBM Information Management Group (part of IBM Software Group). Her specialties include XML, the Web, and databases. She has developed standards that support data for grid environments at the Global Grid Forum. In addition to working as an IBM product software developer, she has also worked as an Internet specialist, a data analyst, and an application designer and developer. She has also co-authored a book on the Web and published articles on transaction processing and XML. She is a member of the IBM Academy of Technology.



Michael Schenker (mschenk@us.ibm.com), IBM DB2 for z/OS Optimizer Software Engineer, IBM

Author Photo: Michael SchenkerMichael Schenker has a masters degree in computer science acquired at the University of Applied Science in Leipzig/Germany. He has 7 years of work experience with IBM. During his career Michael worked on several database-related products starting as an intern to work on DB2 Information Integration with focus on non-relational wrappers. He joined IBM as full-time employee at the end of 2003. In 2006 Michael joined the database tools organization and worked on the DB2 Web service provider functionality in IBM Data Studio Developer. He gained a lot of expertise in SOA and Web technologies during that time . Recently Michael joined the DB2 for z/OS optimizer team but is still interested in SOA and Web technologies in and around DB2.



27 April 2010 (First published 13 October 2009)

Also available in Chinese Russian Japanese Vietnamese Portuguese Spanish

27 April 2010 - Authors updated and replaced the jsonx.zip download file (see Download) which included changes to:

  • Prevent an OutOfMemory exception in the xml2json() function when multi-byte characters occur in the document
  • Prevent the xml2json() function cutting off of trailing characters in the resulting CLOB in case the document contains multi-byte characters
  • Support an optional properties file that defines the output format of the xml2json() function

Introduction

JavaScript (defined in the ECMAScript Language Specification in ECMA) is a scripting language first implemented in Netscape Navigator (a Web browser) to enhance the processing of Web pages. JSON (defined in RFC 4627 at the IETF) is a format that represents JavaScript data structures, such as objects and arrays, as serialized text.

Frequently used terms

  • Ajax: Asynchronous JavaScript + XML
  • API: Application programming interface
  • DBMS: Database Management System
  • DOM: Document Object Model
  • HTTP: Hypertext Transfer Protocol
  • IETF: Internet Engineering Task Force
  • RFC: Request For Comments
  • RSS: Really Simple Syndication
  • SAX: Simple API for XML
  • SOA: Service Oriented Architecture
  • W3C: World Wide Web Consortium
  • XHTML: Extensible HyperText Markup Language
  • XML: Extensible Markup Language
  • XSLT: Extensible Stylesheet Language Transformations

While XML (defined in the XML 1.0 specification at the W3C) is a common representation for message exchange between loosely coupled clients and servers, JSON is often used to achieve the same effect. One of the reasons for the adoption of JSON is that JSON objects are easy to manipulate using scripting languages such as JavaScript, Python, or Ruby.

The idea of storing and querying XML in databases came about after the use of XML for data exchange became prevalent. Similarly it has become common to manipulate and exchange JSON but not to store it yet. However, JSON document-oriented database management systems have started to appear. For example, Apache CouchDB is strongly aligned with JSON through its interfaces and storage.

In this article, we introduce the notion of exchanging JSON as XML in order to take advantage of XML processors, XML appliances, XML storage (normally present in XML databases such as DB2 pureXML), and other XML technologies such as XQuery and XSLT. To achieve that, we will introduce an XML format called JSONx, describe a friendly JSON-to-XML notation, and explain the differences between the JSONx and friendly formats.

By following the steps in the article and accompanying download, you can build a queryable and indexed JSON store that is based on the DB2 pureXML sample database. This article is the first in a series of three that illustrate how to build a JSON-based three-tiered pureXML application incorporating JSON, Web services, and OpenSocial gadgets.

Figure 1. Overview of the Universal Services architecture
Diagram of the Universal Services architecture with user interaction, JSON Universal Services, and data

Universal Services are a simple but fixed set of database operations that allow the querying and modification of XML data, stored in a pureXML column of a DB2 database. These database operations allow you to insert, update, delete, and query data exposed as Web services through the Data Web Service mechanism. See Resources for more information about the Universal Services for pureXML. The JSON Universal Services for pureXML expose the same database operations to clients, but in JSON instead of XML, while continuing to work with XML on the server side. The client application is not aware that incoming and outbound JSON is being transformed into XML on the server side in the database.

The article also explores the choices that you can make to represent JSON as XML, and then goes on to describe some scenarios and how IBM DB2 pureXML can be applied in such scenarios. The article is accompanied by a download (called the JSONx bundle) that you can use to build a sample JSONx pureXML database. It can form a foundation for JSONx applications. Included in the download are two DB2 user-defined functions that convert between JSON and JSONx and vice-versa.

JSON

Develop skills on this topic

This content is part of progressive knowledge paths for advancing your skills. See:

JSON is a text-based, human-readable format that is used to exchange data between clients and servers. It provides developers an exchange format that maps directly to the data structures they use. For that effect, JSON defines the following main data structures: number, string , boolean (true and false), array (an ordered sequence of values), object (collection of key value pairs), and null.

Listing 1 illustrates a JSON object that describes a customer. Nested inside the customerinfo object are two objects that define the customer ID (cid) and name. The customerinfo object also contains two structured objects to define the customer address and phone numbers.

Listing 1. Customer information for Kathy Smith in JSON
{
  "customerinfo" : {
	"cid" : 1000 ,
	"name" : "Kathy Smith" ,
	"addr" : {
	  "country"    : "Canada" ,
	  "street"     : "5 Rosewood" ,
	  "city"       : "Toronto" ,
	  "prov-state" : "Ontario" ,
	  "pcode-zip"  : "M6W 1E6"
	} ,
	"phone" : {
	  "work" : "416-555-1358"
	}
  }
}

A typical JSON scenario

A typical use case for JSON is to have a Web application interchanging data with an API such as Yahoo Web Services or the Twitter API. In this scenario, a Web application makes use of asynchronous JavaScript requests (Ajax) to communicate JSON with the Web service that exposes the API.

Frequently, such an API allows the application to choose the interchange format. Common supported formats include XML, pre-defined XML standards such as RSS and Atom, and JSON. For examples of such formats, refer to Appendix A.

Freedom to choose the format used to communicate between the application and the Web service enables developers to speed up the development process. However, this raises questions regarding the maintenance of an infrastructure that supports multiple formats concurrently. The list below includes some of the options for data storage:

  • Use a document-centric JSON database such as Apache CouchDB.
  • Use a relational database by shredding and reconstructing JSON for each request.
  • Use XML native storage (storing JSONx) and offer interfaces that expose and process JSON.

Figure 2 illustrates examples for each of these data storage options. (View a larger version of Figure 2.)

Figure 2. Different ways to store JSON
Three ways to store JSON: In a JSON database, in a relational database, and as JSONx (XML storage)

All these approaches have their strengths and weaknesses. This article does not analyze those differences but concentrates on a scenario where using pureXML is the most convenient approach for a specific application. Some reasons that might make XML the most convenient approach include:

  • The rest of the infrastructure is already using XML and SOA
  • The existence of XML appliances (such as IBM Websphere® DataPower®) and XML technologies that can be extended to work with JSONx

Contrasting JSON with XML

When developers use XML for exchange, they typically navigate the XML through XML DOM or SAX. The ability to work with the same JSON data structure for manipulation and for exchange simplifies the development process by providing declarative access to the object in the host programming language of the application.

The focus of XML is to provide a self-defining exchange notation optionally associated with a rigorous schema that itself can be exchanged. XML also provides a variety of features, such as namespaces, compression, digital signatures, and security, together with declarative languages such as XQuery and XSLT that can be invoked from the C, Java™, and Ruby programming languages to manipulate one or more XML documents.

JSON does not have the variety of features that XML has.

Representing JSON in XML

The focus of this article is to show how to generate an XML format that is isomorphic (structurally identical) to any JSON document, in other words, how to create a universal mapping between any JSON document and any XML document.

To achieve an effective mapping between any JSON document and XML, you have to consider the differences between both formats. This section explores a rather intuitive but non-isomorphic mapping between XML and JSON so you can understand such differences. Table 1 describes a non-exhaustive example of such mapping.

Table 1. Possible mapping for friendly XML
PatternJSONXML Description
1{"foo" : "bar"} <foo> bar </foo>Object with value type string
2{"foo" : true} <foo> <true/> </foo>Object with value type true
3{"foo" : { "true" : null }} <foo> <true/> </foo>Object with a nested Object with value type null
4{"foo bar!" : true} Error: "foo bar!" is not a valid QNameObject with value type false fails to convert because key contains illegal characters for an XML QName (for example, space and exclamation mark)
5{"foo" : null} <foo/>Object with value type null
6{"foo": { "bar" : null}}<foo> <bar/> </foo> Nested objects with leaf node of type null
7{"foo": { "bar" : [null, false]}} <foo> <bar> <null/> <false/></bar></foo> Object with nested array

We refer to a mapping notation that uses application specific element and attribute names, instead of the canonical JSONx names, as the friendly format for JSON. Many shortcomings exist in this mapping. Patterns 2 and 3 have different structures and yet are serialized into the same XML document, making it impossible to reconstruct the same JSON document from the generated XML. The object key of Pattern 4 contains characters that are not valid for an XML QName. Some techniques might be used to overcome these limitations, but they would not exhibit the same strengths as an isomorphic notation such as JSONx.


JSONx

Canonical JSON XML (JSONx) is introduced as a format that is isomorphic to JSON. Therefore the problems noted in the previous section with friendly notations do not apply in JSONx.

In Listing 2, you can see the customer information for Kathy Smith, previously represented in JSON in Listing 1, as JSONx.

Listing 2. Customer information for Kathy Smith in Canonical JSON XML (JSONx)
<json:object xmlns:json="http://www.ibm.com/xmlns/prod/2009/jsonx">
  <json:object name="customerinfo">
	<json:number name="cid">1000</json:number>
	<json:string name="name">Kathy Smith</json:string>
	<json:object name="addr">
	  <json:string name="country">Canada</json:string>
	  <json:string name="street">5 Rosewood</json:string>
	  <json:string name="city">Toronto</json:string>
	  <json:string name="prov-state">Ontario</json:string>
	  <json:string name="pcode-zip">M6W 1E6</json:string>
	</json:object>
	<json:object name="phone">
	  <json:string name="work">416-555-1358</json:string>
	</json:object>
  </json:object>
</json:object>

JSONx is a suitable format for systems that deal with XML data but need to be extended with support for JSON. However, it does not represent a one-size-fits-all solution for converting JSON to XML. In the previous example, one could specify the original information for Kathy Smith in a friendly format as follows:

Listing 3. Customer information for Kathy Smith in a possible friendly XML format
<customerinfo>
  <cid>1000</cid>
  <name>Kathy Smith</name>
  <addr>
	<country>Canada</country>
	<street>5 Rosewood</street>
	<city>Toronto</city>
	<prov-state>Ontario</prov-state>
	<pcode-zip>M6W 1E6</pcode-zip>
  </addr>
  <phone>
	<work>416-555-1358</work>
  </phone>
</customerinfo>

Using JSONx instead of the friendly format promotes code-reuse, common tooling, transformation, and filtering capabilities that are normally associated with using a standard. One might still choose to create a view that exposes the document in another more friendly format using XML technologies such as XSLT and XQuery.

JSONx allows the use of existing XML infrastructure without having to customize the solution to process and store JSON.


JSONx bundle

This section will provide a brief overview of a JSONx bundle (a collection of scripts and code) for DB2 pureXML that shows how to:

  • Transform JSON into JSONx
  • Transform JSONx into JSON
  • Import JSON documents as JSONx
  • Store JSONx
  • Index JSONx for performance gains
  • Expose JSONx in a relational format
  • Convert JSONx to a friendly XML notation
  • Join JSONx documents (optionally with relational predicates) using XMLQuery

For a more detailed explanation of what is in the JSONx bundle, refer to the readme file that is packaged with the bundle.

Prerequisites

To install and run the JSONx bundle, it is necessary to have DB2 v9.5 or later installed. You can download DB2 Express-C, the open edition of DB2 that includes pureXML, making it a fully-functional, relational and XML data server (see the Resources section for the link to the download site).

Even though this package might run in other operating systems, it was tested with Ubuntu Jaunty Jackalope 9.04 and Microsoft® Windows® XP SP2.

Table structure

In the previous section, you were introduced to the customer information of Kathy Smith, both in JSON and JSONx. In Table 2, you see the customer table where the information is stored:

Table 2: JSONXMLADMIN.CUSTOMER Table
Column name Data type Description
CID INTEGER Customer ID
INFOXML A record containing personal information about the customer
COMMENTVARCHAR(256)A small textual identifier regarding the customer

Two other tables exist in the JSONx bundle: Product and PurchaseOrder.

The product table contains product information in an XML column named DESCRIPTION, as well as the unique identifier for that product:

Table 3: JSONXMLADMIN.PRODUCT Table
Column name Data type Description
PID INTEGER Product ID
DESCRIPTION XML A record containing personal information about the product

PurchaseOrder describes a transaction where a customer buys one or more products. An interesting detail about this table is that the customer identifier is not contained within the original JSON file but in a separate relational column. By using JSONx and DB2 pureXML, you can join data from two (or more) distinct JSON documents, and also join relational records with JSON documents. For a specific example on how to perform such a join, refer to the procedural code contained in the JSONx bundle.

Table 4: JSONXMLADMIN.PURCHASEORDER Table
Column name Data type Description
POID INTEGER Purchase order ID
CUSTID INTEGER The associated customer ID
PORDER XML A record containing information regarding the purchase order

Tranforming JSON into JSONx with Java user-defined functions

The JSONx bundle will register two Java user-defined functions (UDFs) in DB2 that enable converting JSON into JSONx and vice-versa. Listing 4 illustrates a very simple invocation of the user-defined function that transforms JSON into JSONx.

Listing 4. Invoking JSONTOXML Java user-defined function
SELECT JSONXML.JSONTOXML('{"foo": "bar"}') FROM SYSIBM.SYSDUMMY1

Listing 5 shows the output from the SELECT statement in Listing 4.

Listing 5. Output produced by the invocation in Listing 4
<json:object xmlns:json="http://www.ibm.com/xmlns/prod/2009/jsonx">
 <json:string name="foo"> bar </json:string>
</json:object>

To transform JSONx into JSON, invoke the XMLTOJSON stored procedure. Listing 6 shows an example where the same JSON document that was used in Listing 4 is serialized into JSONx and then back to JSON.

Listing 6. JSON to JSONx and then back to JSON
SELECT JSONXML.XMLTOJSON(JSONXML.JSONTOXML('{"foo": "bar"}')) FROM SYSIBM.SYSDUMMY1

Joining JSONx documents

One of the exciting features that exists in DB2 pureXML is the variety of options for joining data. Storing JSONx in DB2 pureXML means that you can now join your JSON data just as you did with your XML documents. Listing 7 shows how to use a join with both relational and XML predicates and return, for each Purchase Order, the POID, and customer name.

Listing 7. Joining JSONx with both relational and XML predicates
SELECT
  POID, 
  XMLCAST (
	XMLQUERY(
	  ''declare default element namespace "http://www.ibm.com/xmlns/prod/2009/jsonx";
	  data( 
		$INFO/object/object[@name="customerinfo"]/string[@name="name"]/text() )''
	  ) AS VARCHAR(32) 
	) AS CUSTNAME
FROM
  JSONXMLADMIN.PURCHASEORDER , 
  JSONXMLADMIN.CUSTOMER
WHERE
  XMLEXISTS(
	''declare default element namespace "http://www.ibm.com/xmlns/prod/2009/jsonx";
	$INFO/object/object[@name="customerinfo"]/number[@name="cid"][. = $CUSTID]'' 
  )

DB2 9.7

The JSONx bundle also illustrates some of the newest additions in DB2 v9.7 pureXML. When creating a view on a JSONx document with XMLTABLE, DB2 v9.7 will pick up XML indexes that apply, thus making the query much faster than in previous versions.

Listing 8. Exposing JSON as a relational view with XMLTable
CREATE VIEW JSONXMLADMIN.RELCUSTOMER(CID, NAME, PHONE) AS 
 SELECT  *
 FROM
  XMLTABLE(
   XMLNAMESPACES (DEFAULT 'http://www.ibm.com/xmlns/prod/2009/jsonx'),
   'db2-fn:xmlcolumn("JSONXMLADMIN.CUSTOMER.INFO")'
    COLUMNS
	  "CID" INTEGER 
	  PATH '/object/object[@name="customerinfo"]/number[@name="cid"]/xs:double(.)' ,
	  "NAME" VARCHAR(32) 
	  PATH '/object/object[@name="customerinfo"]/string[@name="name"]/text()' ,
	  "PHONE" VARCHAR(32)
	  PATH '/object/object[@name="customerinfo"]/object[@name="phone"][1]/*[1]/text()'
	)

It is also possible to produce JSONx from relational data using the SQL/XML publishing functions.

Another new feature is the support of the XML datatype in user-defined functions allowing the manipulation of XML documents. When you use DB2 v9.7 to run the JSONx bundle, you use two other new features to produce the output: indexing on the view and an XML user-defined function.

The UDF that is included in the JSONx bundle makes use of the XQuery Update Facility to transform JSONx into a format that is adequate to represent the specific subset of JSON that will be imported once the execution of the JSONx sample database is finished. While this does not represent a generic or efficient transformation algorithm, Listing 9 shows how one might effectively program advanced XML user-defined functions using DB2 pureXML:

Listing 9. Sample XML UDF used to generate friendly XML
CREATE FUNCTION JSONXMLADMIN.JSONXTOFRIENDLY(JSONX XML)
RETURNS XML
BEGIN ATOMIC
  RETURN XMLQUERY('
	declare namespace json="http://www.ibm.com/xmlns/prod/2009/jsonx";
	copy $n := $JSONX
	modify(
	  for $e in $n//json:*
	  where $e/@name
	  return (
		do rename $e as replace($e/@name," ", "_") ,
		do delete $e/@name
	  )
	)
	return document { $n/json:object/* }
  ');
END

Listing 10 shows how to transform a customer information that has been put in the previous friendly notation back into JSONx.

Listing 10. Transforming Kathy Smith's friendly customer information back to JSONx
SELECT
  XMLQUERY('
	declare namespace json="http://www.ibm.com/xmlns/prod/2009/jsonx";
	copy $n := $friendly
	modify(
	  for $e in $n//*
	  let $name := local-name($e)
	  let $type := 
		if ($name = ("addr", "phone", "customerinfo")) then "json:object"
		else if ($name eq "cid") then "json:number"
		else "json:string"
	  return (
		do rename $e as $type,
		do insert attribute name { $name } into $e
	  )
	)
	return document { 
	  <json:object>
		{ $n } 
	  </json:object>
	}
  ' PASSING JSONXMLADMIN.JSONXTOFRIENDLY(INFO) as "friendly")
  FROM
	JSONXMLADMIN.CUSTOMER
  WHERE
	CID = 1000

Running the JSONx Bundle

Follow the steps listed below to set up the JSONx bundle:

  • To use it on Windows platforms, just extract the jsonx.zip file that is available on the resources section in your file system. On Linux® platforms, unzip the jsonx.zip file using unzip -a -aa jsonx.zip. This will ensure that the right line termination characters are used.
  • On Windows systems, make sure the DB2 command line processor environment is initialized. In Linux-based systems, please check that you are logged in with a user that has access to DB2 (db2inst1 is the default DB2 user).
  • You are ready to run the scripts. (NOTE: Be aware that the start scripts might configure some of the DBMS parameters and it might STOP and RESTART the DBMS to ensure the DBMS can handle the jar-stored procedure. If you are worried about the scripts please review them before running!) Run start.bat on Windows platforms. On Linux platforms, use start.sh. After this script has finished, you should see something similar to Figure 3 in your command prompt or bash. (View a text version of the sample output in Figure 3.)
Figure 3. Sample output of the JSONx bundle
Sample output of the JSONx bundle

At this point, everything is set up and you can take a look at the query results in the output folder. To check if the results created are correct, you can compare the output files in the output folder with the reference output that is located in the reference folder.

If the samples do not match the reference folder, consult the log file located in the logs directory to find what might have caused the execution to fail.

For an illustration of tweets from Twitter in friendly XML and in JSONx, see Appendix A.


Conclusion

In this article, a new format (JSONx) was introduced, enabling the storage of JSON as XML in DB2 pureXML. The differences between JSONx and other friendly formats were outlined. A typical use case for JSON was discussed and the benefits of using pureXML to store JSON were highlighted. Finally, with a JSONx download (the JSONx bundle), you now have a foundation to build pureXML-enabled JSON applications.

The next two articles in this series will focus on exposing the JSONx sample database (created in the JSONx bundle) through JSON Universal Services and then focus on the creation of the presentation layer with Open-Social Gadgets that rely on the JSON Universal Services as a back-end.

A future possibility is to consider ways for Web developers to access JSON documents (stored as JSONx) with JSONPath.


Acknowledgment

We'd like to thank Brien Muschett and William Palma for their contributions.


Appendix A

As referenced in A typical JSON scenario, many Web services provide multiple formats to display the information. Industry standards such as Atom and RSS, JSON, or even friendly XML notations are examples of such formats. Twitter offers an API to access tweets by ID. In this appendix, we show the results of issuing two requests to Twitter using curl, one in JSON and one in friendly XML. Then we demonstrate how to convert the friendly XML to JSONx.

In Listing 11, we use curl to call for information about an individual message (the tweet whose id is 2311383114) that was posted to twitter.com. To retrieve the tweet information (its status), you can invoke curl from your terminal as in Listing 11, and the status of that tweet is returned in JSON format. (Note: The invocation of curl in Listings 11 and 12 is split across two lines for formatting purposes.)

Listing 11. Retrieve status with ID 2311383114 in the JSON format
purexml@watson.ibm.com:~$ curl 
http://purexmltest:3ce3ac99@twitter.com/statuses/show.xml?id=2311383114

{
    "text": "Hello World!",
    "in_reply_to_user_id": null,
    "user": {
        "following": null,
        "favourites_count": 0,
        "profile_sidebar_fill_color": "e0ff92",
        "description": null,
        "verified": false,
        "utc_offset": null,
        "statuses_count": 1,
        "profile_sidebar_border_color": "87bc44",
        "followers_count": 0,
        "created_at": "Wed Jun 24 14:18:32 +0000 2009",
        "url": null,
        "name": "pureXML TEST",
        "friends_count": 0,
        "profile_text_color": "000000",
        "protected": false,
        "profile_image_url": "http:\/\/s3.amazonaws.com\/twitter_production
		\/profile_images\/280225879\/twitterxml-1_bigger_normal.png",
        "profile_background_image_url": "http:\/\/static.twitter.com
		\/images\/themes\/theme1\/bg.gif",
        "notifications": null,
        "time_zone": null,
        "profile_link_color": "0000ff",
        "screen_name": "purexmltest",
        "profile_background_tile": false,
        "profile_background_color": "9ae4e8",
        "location": null,
        "id": 50316451
    },
    "favorited": false,
    "created_at": "Wed Jun 24 15:04:13 +0000 2009",
    "in_reply_to_screen_name": null,
    "truncated": false,
    "id": 2311383114,
    "in_reply_to_status_id": null,
    "source": "web"
}

Alternatively, you can retrieve the same document in Atom, RSS, or in a friendly XML notation. In Listing 12, you invoke the same function to retrieve the status, but this time in the friendly XML notation:

Listing 12. Retrieve the status with ID 2311383114 in friendly XML format
purexml@watson.ibm.com:~$ curl 
http://purexmltest:3ce3ac99@twitter.com/statuses/show.xml?id=2311383114

<?xml version="1.0" encoding="UTF-8"?>
<status>
  <created_at>Wed Jun 24 15:04:13 +0000 2009</created_at>
  <id>2311383114</id>
  <text>Hello World!</text>
  <source>web</source>
  <truncated>false</truncated>
  <in_reply_to_status_id></in_reply_to_status_id>
  <in_reply_to_user_id></in_reply_to_user_id>
  <favorited>false</favorited>
  <in_reply_to_screen_name></in_reply_to_screen_name>
  <user>
    <id>50316451</id>
    <name>pureXML TEST</name>
    <screen_name>purexmltest</screen_name>
    <location></location>
    <description></description>
    <profile_image_url>http://s3.amazonaws.com/twitter_production/
	profile_images/280225879/twitterxml-1_bigger_normal.png</profile_image_url>
    <url></url>
    <protected>false</protected>
    <followers_count>0</followers_count>
    <profile_background_color>9ae4e8</profile_background_color>
    <profile_text_color>000000</profile_text_color>
    <profile_link_color>0000ff</profile_link_color>
    <profile_sidebar_fill_color>e0ff92</profile_sidebar_fill_color>
    <profile_sidebar_border_color>87bc44</profile_sidebar_border_color>
    <friends_count>0</friends_count>
    <created_at>Wed Jun 24 14:18:32 +0000 2009</created_at>
    <favourites_count>0</favourites_count>
    <utc_offset></utc_offset>
    <time_zone></time_zone>
    <profile_background_image_url>
http://static.twitter.com/images/themes/theme1/bg.gif</profile_background_image_url>
    <profile_background_tile>false</profile_background_tile>
    <statuses_count>1</statuses_count>
    <notifications></notifications>
    <verified>false</verified>
    <following></following>
  </user>
</status>

Listing 13 shows a sample invocation of the DB2 UDF that converts JSON into JSONx on the results yielded in Listing 12.

Listing 13. JSONx representation of the JSON document that was yielded in Listing 12.
db2 => SELECT JSONXML.JSONTOXML('{"text":"Hello World!",
"in_reply_to_user_id":null,"user":{"following":null,
"favourites_count":0,"profile_sidebar_fill_color":"e0ff92",
"description":null,"verified":false,"utc_offset":null,
"statuses_count":1,"profile_sidebar_border_color":"87bc44",
"followers_count":0,"created_at":"Wed Jun 24 14:18:32 +0000 2009",
"url":null,"name":"pureXML TEST","friends_count":0,
"profile_text_color":"000000","protected":false,"profile_image_url":
"http:\/\/s3.amazonaws.com\/twitter_production
\/profile_images\/280225879\/twitterxml-1_bigger_normal.png",
"profile_background_image_url":"http:\/\/static.twitter.com
\/images\/themes\/theme1\/bg.gif",
"notifications":null,"time_zone":null,"profile_link_color":"0000ff",
"screen_name":"purexmltest","profile_background_tile":false,
"profile_background_color":"9ae4e8","location":null,"id":50316451},
"favorited":false,"created_at":"Wed Jun 24 15:04:13 +0000 2009",
"in_reply_to_screen_name":null,"truncated":false,"id":2311383114,
"in_reply_to_status_id":null,"source":"web"}') FROM SYSIBM.SYSDUMMY1

<json:object xmlns:json="http://www.ibm.com/xmlns/prod/2009/jsonx">
  <json:string name="text">Hello World!</json:string>
  <json:null name="in_reply_to_user_id"/>
  <json:object name="user">
    <json:null name="following"/>
    <json:number name="favourites_count">0</json:number>
    <json:string name="profile_sidebar_fill_color">e0ff92</json:string>
    <json:null name="description"/>
    <json:boolean name="verified">false</json:boolean>
    <json:null name="utc_offset"/>
    <json:number name="statuses_count">1</json:number>
    <json:string name="profile_sidebar_border_color">87bc44</json:string>
    <json:number name="followers_count">0</json:number>
    <json:string name="created_at">Wed Jun 24 14:18:32 +0000 2009</json:string>
    <json:null name="url"/>
    <json:string name="name">pureXML TEST</json:string>
    <json:number name="friends_count">0</json:number>
    <json:string name="profile_text_color">000000</json:string>
    <json:boolean name="protected">false</json:boolean>
    <json:string name="profile_image_url"
	>http://s3.amazonaws.com/twitter_production/profile_images
	/280225879/twitterxml-1_bigger_normal.png</json:string>
    <json:string name="profile_background_image_url"
	>http://static.twitter.com/images/themes/theme1/bg.gif</json:string>
    <json:null name="notifications"/>
    <json:null name="time_zone"/>
    <json:string name="profile_link_color">0000ff</json:string>
    <json:string name="screen_name">purexmltest</json:string>
    <json:boolean name="profile_background_tile">false</json:boolean>
    <json:string name="profile_background_color">9ae4e8</json:string>
    <json:null name="location"/>
    <json:number name="id">50316451</json:number>
  </json:object>
  <json:boolean name="favorited">false</json:boolean>
  <json:string name="created_at">Wed Jun 24 15:04:13 +0000 2009</json:string>
  <json:null name="in_reply_to_screen_name"/>
  <json:boolean name="truncated">false</json:boolean>
  <json:number name="id">2311383114</json:number>
  <json:null name="in_reply_to_status_id"/>
  <json:string name="source">web</json:string>
</json:object>

Download

DescriptionNameSize
JSONx Bundlejsonx.zip143KB

Resources

Learn

Get products and technologies

  • DB2 Express-C: Download this no-charge community edition of the DB2 data server.
  • IBM trial software: Build your next development project with software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into XML on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=XML, Information Management, Web development
ArticleID=434764
ArticleTitle=Build a pureXML and JSON application, Part 1: Store and query JSON with DB2 pureXML
publish-date=04272010