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

Time:11-19

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
  • Related