sql - SQL 查找特定节点并将其值 (json) 作为 excel 格式的结果返回

Question

有一个带有 json 字符串结果的 xml 列 (ArrayOfString)。我需要找到特定的节点并获得它们的价值。

<ArrayOfstring xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
    <string>
        [report_submit_1, {
        "total": 14,
        "report": [
                {
                "date": "11/02/2018",
                etc...
                },
                {
                "date": "01/02/2019",
                etc...
                },
                {
                "date": "12/12/2018",
                etc...
                },
                etc...
            ]
        }]
    </string>
    <string>
        [something_else, {
        "total": 1,
        "report": [
                {
                "date": "12/15/2018",
                etc...
                },
                {
                "date": "03/25/2019",
                etc...
                },
                {
                "date": "04/12/2019",
                etc...
                },
                etc...
                ]
        }]
    </string>
    <string>
        [report_submit_2, {
        "total": 22,
        "report": [
                {
                "date": "05/22/2019",
                etc...
                },
                {
                "date": "01/02/2019",
                etc...
                },
                {
                "date": "12/12/2018",
                etc...
                },
                etc...
                ]
        }]
    </string>
</ArrayOfstring>

SELECT 
Xml.value('(.[1])', 'VARCHAR(MAX)') AS Results
FROM tableA 
WHERE Xml.exist('(/ns:ArrayOfString/ns:string[contains(.,"report_submit")])') = 0

希望在 sql 结果中将此“解析”为 xls 样式，因此可以将其保存为带有标题的 excel 文件

标签： sqljsonexcelxmlparsing