json - JSON解析表列SQL Server 2016的键

Question

我们正在接收此 JSON 文档并使用 Kingswaysoft JSON Source 组件导入 SQL Server 2016

{
    "Program": "USRMI",
    "Transaction": "GetUserInfo",
    "Metadata": {
        "Field": [
            {
                "@name": "USERID",
                "@type": "A",
                "@length": 10,
                "@description": "User"
            },
            {
                "@name": "COMPNO",
                "@type": "N",
                "@length": 3,
                "@description": "Company"
            },
        ]
    },
    "MIRecord": [
        {
            "NameValue": [
                {
                    "Name": "USERID",
                    "Value": "JOEBLO88"
                },
                {
                    "Name": "COMPNO",
                    "Value": "999"
                },

            ],
            "RowIndex": 0
        }
        {
            "NameValue": [
                {
                    "Name": "USERID",
                    "Value": "JOEBLO55"
                },
                {
                    "Name": "COMPNO",
                    "Value": "998"
                },
            ],
            "RowIndex": 1
        }       
    ]
}

并在表中有一个列，如下所示：

{"Name": "USERID","Value": "JOBLO88"}
{"Name": "COMPNO","Value": "988"}
{"Name": "USERID","Value": "JOBLO55"}
{"Name": "COMPNO","Value": "955"}

我正在努力寻找任何可用于将其转换为列结构表的语法资源，如下所示

USERID  COMPNO
======  ======
JOBLO88  988
JOBLO55  955

标签： jsonsql-serverdatabasekingswaysoft