Google sheets IMPORTXML Query get attribute with local-name

This is a follow up question to this previous question: Google sheets IMPORTXML Return no Data N/A

Given the following XML (has namespace) I want to query the CODE number inside CPV_CODE.

<CPV_CODE CODE="90524400"/>

With XPATH I would simply use: /CPV_CODE/@CODE but as I have to use local-name (due to namespace) I have no idea how to get it.

I tried

=importxml(url,"//*[local-name() ='CPV_CODE']")

but it does not return the @CODE number.

>Solution :

In your situation, how about the following xpath?

Sample xpath:

//*[local-name()='CPV_MAIN']/*[local-name()='CPV_CODE']/@CODE

Or

//*[local-name()='CPV_MAIN']//@CODE

Modified formula:

=IMPORTXML(A1,"//*[local-name()='CPV_MAIN']/*[local-name()='CPV_CODE']/@CODE")

Or

=IMPORTXML(A1,"//*[local-name()='CPV_MAIN']//@CODE")

• In this case, the URL is put in the cell "A1".

Result:

Note:

• In the URL, it seems that there are 2 CPV_CODE tags. If you want to retrieve <CPV_CODE CODE="90524200"/>, how about the following formula?

    =IMPORTXML(A1,"//*[local-name()='CPV_ADDITIONAL']/*[local-name()='CPV_CODE']/@CODE")
  

  Or

    =IMPORTXML(A1,"//*[local-name()='CPV_ADDITIONAL']//@CODE")
  

Reference:

• IMPORTXML