I'm trying to access an XML elements attribute in Azure KQL having converted it to JSON using parse_xml. However the extractjson function doesn't seem to like the use of the @ notation. See the code snippet below.
let input_xml="<NetAmount currency=\"USD\">150.00</NetAmount>";
let sJson=tostring(parse_xml(input_xml));
let amount=extractjson("$.NetAmount.#text", sJson);
let sCurrency=extractjson($.NetAmount.@currency, sJson);
print input_xml, amount, sJson //, sCurrency;
If you run the above code it will work. However if you comment in the reference to sCurrency in the print statement, it barfs with an error which reads:
There was a problem running your query. Please try again later
Any ideas how you reference the currency attribute in the extractjson function?
CodePudding user response:
you don't need to use extract_json()
, rather you can simply use dynamic object accessors: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/scalar-data-types/dynamic#dynamic-object-accessors
print input_xml = "<NetAmount currency=\"USD\">150.00</NetAmount>"
| project sJson = parse_xml(input_xml)
| project amount = sJson.NetAmount['#text'], currency = sJson.NetAmount['@currency']
amount | currency |
---|---|
150.00 | USD |