json2xml¶

Call or Deploy json2xml ?

✅ You can call this json2xml bigfunction directly from your Google Cloud Project (no install required).

This json2xml function is deployed in bigfunctions GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error).
Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
You may prefer to deploy the BigFunction in your own project if you want to build and manage your own catalog of functions --> Read Getting Started. This is particularly useful if you want to create private functions (for example calling your internal APIs).
For any question or difficulties, please read Getting Started.
Found a bug? Please raise an issue here

Public BigFunctions Datasets are like:

Region	Dataset
`eu`	`bigfunctions.eu`
`us`	`bigfunctions.us`
`europe-west1`	`bigfunctions.europe_west1`
`asia-east1`	`bigfunctions.asia_east1`
...	...

Description¶

Signature

json2xml(json)

Description

Returns XML for given JSON string

Examples¶

1. With valid JSON

EUUSeurope-west1

select bigfunctions.eu.json2xml('{"a": {"b": "foo"}}')

select bigfunctions.us.json2xml('{"a": {"b": "foo"}}')

select bigfunctions.europe_west1.json2xml('{"a": {"b": "foo"}}')

+-------------------+
| xml               |
+-------------------+
| <a><b>foo</b></a> |
+-------------------+

2. With valid JSON and with one key that has empty string as a value

EUUSeurope-west1

select bigfunctions.eu.json2xml('{"a": ""}')

select bigfunctions.us.json2xml('{"a": ""}')

select bigfunctions.europe_west1.json2xml('{"a": ""}')

+---------+
| xml     |
+---------+
| <a></a> |
+---------+

3. With invalid JSON

EUUSeurope-west1

select bigfunctions.eu.json2xml('{"a": ""')

select bigfunctions.us.json2xml('{"a": ""')

select bigfunctions.europe_west1.json2xml('{"a": ""')

+------+
| xml  |
+------+
| null |
+------+

Use cases¶

A practical use case for the json2xml function is converting JSON data stored in BigQuery into an XML format for integration with systems that primarily use XML.

Scenario: You have product data stored in BigQuery in JSON format. A legacy system or a third-party partner requires product data in XML format for processing or integration.

Example:

Imagine your BigQuery table product_data has a column product_info containing JSON data like this:

{"product_id": "12345", "name": "Awesome Gadget", "price": 99.99, "description": "A really cool gadget."}

You can use the json2xml function to convert this JSON data to XML within your BigQuery query:

SELECT bigfunctions.us.json2xml(product_info) AS product_xml
FROM `your_project.your_dataset.product_data`;

This query will produce XML output like this:

<product_info><product_id>12345</product_id><name>Awesome Gadget</name><price>99.99</price><description>A really cool gadget.</description></product_info>

You can then export this XML data from BigQuery for use in the target system.

Other Use Cases:

Data Transformation for API Integration: Convert JSON responses from APIs to XML for consumption by services that expect XML.
Generating XML Reports: Transform JSON data into structured XML reports for specific business needs.
Data Migration: Migrate data stored in JSON format to systems that use XML.
Interoperability between Systems: Facilitate data exchange between systems that use different data formats (JSON and XML).

By using json2xml directly within BigQuery, you avoid the need to export the JSON data and process it externally, simplifying the data transformation process and improving efficiency.