英文:
How to get @odata.etag value from JSON in SQL Server table
问题
SELECT
TableA.etag
FROM
OPENJSON((SELECT * FROM @json))
WITH (
etag nvarchar(300) '$."@odata.etag"'
) AS TableA
英文:
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.
答案1
得分: 1
你正在使用 `JSON_VALUE` 函数从 JSON 字符串中提取 `@odata.etag` 值。然而，在你的情况下，错误发生是因为 `OPENJSON` 函数期望一个 JSON 表达式，而不是一个表变量。
英文:
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;
答案2
得分: 0
最佳解决方案来自Thom A. 非常感谢。
SELECT
TableA.etag
FROM OPENJSON((SELECT * FROM @json))
WITH (
etag nvarchar(300) '$."@odata.etag"'
) AS TableA
英文:
Best solution from Thom A. Many thanks.
SELECT
TableA.etag
FROM OPENJSON((SELECT * FROM @json))
WITH (
etag nvarchar(300) '$."@odata.etag"'
) AS TableA
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论