How to get @odata.etag value from JSON in SQL Server table

Question

Does anybody knows how to get @odata.etag value from a JSON into a SQL Server table? Many thanks!

JSON string:

{
    "@odata.context": "https://mycompany.com/Tenant/ODataV4/$metadata#Company('mycompany')/ServiceHeader/$entity",
    "@odata.etag":"W/\"JzQ0O0NLZ0lhMkdDdkw3ZU1PbUx6M1M5V1Y4OUtUZUI5OXZOUCtjM2FRZmQ1N0E9MTswMDsn\"",
    "Document_Type": "Order",
    "No": "1234",
    "Customer_No": "1234",
    "Your_Reference": "",
    "Payment_Terms_Code": "01",
    "Name": "Company ABC",
    "Contact_No": "4567",
    "Status": "Pending",
    "Release_Status": "Open",
    "Description": "", 
    "Contract_No": "",
    "Service_Order_Type": "",
    "Last_Modified_Date_Time": "2023-06-30T14:40:39.307Z",
    "FBID": "",
    "FBWorkOrderNo": "",
    "Bill_to_Customer_No": "1234"
}

I have tried to use this code:

SELECT
    TableA.etag
FROM
    OPENJSON((SELECT * FROM @json))
    WITH (
            etag nvarchar(300) '$.@odata.etag'
         ) AS TableA

I get this error:

JSON path is not properly formatted. Unexpected character '@' is found at position 2.

select TableA.etag from OPENJSON((select * from @json)) with ( etag nvarchar(300) '"@odata.etag"' ) as TableA This gives error: JSON path is not properly formatted. Unexpected character '"' is found at position 0. — Daniel de Jong, Jul 03 '23 at 08:34

score 1 · Answer 1 · answered Jul 03 '23 at 11:47

You are extracting the @odata.etag value from the JSON string using the JSON_VALUE function. However, in your case, the error occurs because the OPENJSON function expects a JSON expression, not a table variable.

DECLARE @json NVARCHAR(MAX) = '{
    "@odata.context": "https://mycompany.com/Tenant/ODataV4/$metadata#Company(''mycompany'')/ServiceHeader/$entity",
    "@odata.etag":"W/\"JzQ0O0NLZ0lhMkdDdkw3ZU1PbUx6M1M5V1Y4OUtUZUI5OXZOUCtjM2FRZmQ1N0E9MTswMDsn\"",
    "Document_Type": "Order",
    "No": "1234",
    "Customer_No": "1234",
    "Your_Reference": "",
    "Payment_Terms_Code": "01",
    "Name": "Company ABC",
    "Contact_No": "4567",
    "Status": "Pending",
    "Release_Status": "Open",
    "Description": "", 
    "Contract_No": "",
    "Service_Order_Type": "",
    "Last_Modified_Date_Time": "2023-06-30T14:40:39.307Z",
    "FBID": "",
    "FBWorkOrderNo": "",
    "Bill_to_Customer_No": "1234"
}';

SELECT JSON_VALUE(@json, '$."@odata.etag"') AS etag;

score 0 · Answer 2 · answered Jul 03 '23 at 11:56

0

Best solution from Thom A. Many thanks.

SELECT
TableA.etag
FROM OPENJSON((SELECT * FROM @json))
    WITH (
    etag nvarchar(300) '$."@odata.etag"'
)  AS TableA

answered Jul 03 '23 at 11:56

Daniel de Jong

13
4

How to get @odata.etag value from JSON in SQL Server table

2 Answers2