xml2json¶
xml2json(xml)
Description¶
Returns JSON as a string for given XML string
Usage¶
Call or Deploy xml2json
?
Call xml2json
directly
The easiest way to use bigfunctions
xml2json
function is deployed in 39 public datasets for all of the 39 BigQuery regions.- It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
- (You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)
Public BigFunctions Datasets
Region | Dataset |
---|---|
eu |
bigfunctions.eu |
us |
bigfunctions.us |
europe-west1 |
bigfunctions.europe_west1 |
asia-east1 |
bigfunctions.asia_east1 |
... | ... |
Deploy xml2json
in your project
Why deploy?
- You may prefer to deploy
xml2json
in your own project to build and manage your own catalog of functions. - This is particularly useful if you want to create private functions (for example calling your internal APIs).
- Get started by reading the framework page
Deployment
xml2json
function can be deployed with:
pip install bigfunctions
bigfun get xml2json
bigfun deploy xml2json
Examples¶
select bigfunctions.eu.xml2json("\u003ca\u003e\u003cb\u003efoo\u003c/b\u003e\u003c/a\u003e")
select bigfunctions.us.xml2json("\u003ca\u003e\u003cb\u003efoo\u003c/b\u003e\u003c/a\u003e")
select bigfunctions.europe_west1.xml2json("\u003ca\u003e\u003cb\u003efoo\u003c/b\u003e\u003c/a\u003e")
+-------------------+
| json |
+-------------------+
| {"a":{"b":"foo"}} |
+-------------------+
select bigfunctions.eu.xml2json("\u003ca\u003e\u003c/a\u003e")
select bigfunctions.us.xml2json("\u003ca\u003e\u003c/a\u003e")
select bigfunctions.europe_west1.xml2json("\u003ca\u003e\u003c/a\u003e")
+----------+
| json |
+----------+
| {"a":""} |
+----------+
select bigfunctions.eu.xml2json("\u003ca\u003e\u003c/a")
select bigfunctions.us.xml2json("\u003ca\u003e\u003c/a")
select bigfunctions.europe_west1.xml2json("\u003ca\u003e\u003c/a")
+------+
| json |
+------+
| null |
+------+
Use cases¶
Let's say you have a BigQuery table that stores product information, but some of that information is stored in XML format within a string column. You want to analyze this data using BigQuery's powerful SQL capabilities, but working directly with XML in SQL can be cumbersome. The xml2json
function provides a solution.
Scenario:
Your table products
has columns like product_id
, product_name
, and product_details
. The product_details
column contains XML data like this:
<product_details>
<color>Red</color>
<size>Large</size>
<price>25.99</price>
</product_details>
Use Case with xml2json
:
You can use xml2json
to convert the XML data into JSON within your SQL query, making it easier to access specific elements:
SELECT
product_id,
product_name,
JSON_VALUE(bigfunctions.us.xml2json(product_details), '$.product_details.color') AS color,
JSON_VALUE(bigfunctions.us.xml2json(product_details), '$.product_details.size') AS size,
CAST(JSON_VALUE(bigfunctions.us.xml2json(product_details), '$.product_details.price') AS NUMERIC) AS price
FROM
products;
This query uses xml2json
to convert the product_details
XML into a JSON string. Then, JSON_VALUE
extracts the color
, size
, and price
values using JSONPath expressions. This transforms the XML data into a more manageable format for analysis within BigQuery.
Other Potential Use Cases:
- Data Transformation for downstream applications: Convert XML data to JSON before exporting it to other systems that work better with JSON.
- Simplifying complex XML structures: Transform complex, nested XML into a flatter JSON structure for easier querying and reporting.
- API Integration: If an API returns data in XML format,
xml2json
can be used to convert the response into JSON within BigQuery for analysis. - Log Processing: If log files are stored in XML format, this function can convert them to JSON for easier parsing and analysis within BigQuery.
By converting XML to JSON within BigQuery using xml2json
, you unlock the power of BigQuery's JSON functions and make complex XML data more accessible for analysis and processing.
Need help or Found a bug?
Get help using xml2json
The community can help! Engage the conversation on Slack
We also provide professional suppport.
Report a bug about xml2json
If the function does not work as expected, please
- report a bug so that it can be improved.
- or open the discussion with the community on Slack.
We also provide professional suppport.