Question 199666 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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

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) '[email protected]'
         ) AS TableA

I get this error:

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Best solution from Thom A. Many thanks.

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

  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search