How do i navigate the path of a json object that has namespace prefixes (eg n1:, n7: ) in sql server using OPENJSON?

Question

I want to obtain xml files from an API (I can not get Json formats from this API). Thereafter i want to convert them to JSON and then save then to oneDrive using a logic app. (I intend to create tables from these json files in Azure SQL server)

1. My approach so far is trigger the logic app with an HTTP GET response.

2. convert the recieved xml file from step 1 above to json using json(xml(triggerBody())) and save it to oneDrive.

The logic App steps are: 1) GET an HTTP response from the API 2) Save the response as json on oneDrive using the function json(xml(triggerBody()))

here is a copy of the xml file from the API:

<?xml version="1.0" encoding="ISO-8859-1"?>
-<n1:VareResult xmlns:n1="http://ABCC.Schemas.Vare/8.0" xmlns:n7="http://ABCC.Schemas.Common/8.0">
-<n1:Header>
-<n1:EksportType>
 <n7:EksportId>0</n7:EksportId>
 <n7:Eksportmetode/>
 <n7:StartIndex>0</n7:StartIndex>
 <n7:BatchSize>2000</n7:BatchSize>
 <n7:TotalCount>1</n7:TotalCount>
 <n7:VersionNr/>
</n1:EksportType>
-<n1:Result> <n7:ResultValue>Success</n7:ResultValue> </n1:Result>
</n1:Header>
-<n1:VareListe>
-<n1:Vare>
 -<n1:Status>
   <n7:Created>2004-06-27T15:30:57.549</n7:Created>
   <n7:Updated>2019-11-20T09:34:03.008</n7:Updated>
 </n1:Status>
<n1:ABCCNr>10203</n1:ABCCNr>
........
........

Here is its corresponding json file:

{"?xml":{"@version":"1.0","@encoding":"iso-8859-1"},"n1:VareResult":
{"@xmlns:n7":"http://ABCC.Schemas.Common/8.0","@xmlns:n1":"http://ABCC.Schemas.Vare/8.0","n1:Header":
{"n1:EksportType":
{"n7:EksportId":"0","n7:Eksportmetode":null,"n7:StartIndex":"0","n7:BatchSize":"2000","n7:TotalCount":
"1","n7:VersionNr":null},"n1:Result":{"n7:ResultValue":"Success"}},"n1:VareListe":{"n1:Vare":
{"n1:Status":{"n7:Created":"2004-06-27T15:30:57.549","n7:Updated":"2019-11-20T09:34:03.008"},
"n1:ABCCNr":"10203",
........
.......

As you see the json file has namespace prefixes too. I am trying to navigate the path in sql server. Here is the example: Image below shows result from the command: select * FROM OPENJSON(@jsonObject)

I want to access the values for the key n1:VareResult.

I have tried to navigate the path like this: select * FROM OPENJSON (@jsonObject,'$.n1:VareResult' ) But I get the error: "JSON path is not properly formatted. Unexpected character ':' is found at position 4."

Is there an escape sequence that can let me navigate the path even if it has these 'forbidden characters'? If not is there a way i can control the conversion from xml to json in step 2 above so that the namespace prefixes are omitted for each element?

Answer 1

Let me give you an example for this, then you can try somethins like this:

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json,'$.path.to."sub-object"')

Additional reference:

https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15

Hope it helps.

Answer 2

Using the tip from: https://stackoverflow.com/a/45873164/10404094, I added double quotes around the field names in sql server and it worked. Just like this:

select * FROM OPENJSON (@jsonObject, '$."n1:VareResult"."n1:VareListe"."n1:Vare" ') 

This fixed my problem! Thanks stackoverflow!