How do I reference an XML attribute which begins with an @ symbol using KQL extractjson function?

Question

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="150.00";
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?

user_7861807 · Answer

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 = "150.00"
| project sJson = parse_xml(input_xml)
| project amount = sJson.NetAmount['#text'], currency = sJson.NetAmount['@currency']

amount	currency
150.00	USD

How do I reference an XML attribute which begins with an @ symbol using KQL extractjson function?

1 Answers