在 SQL Server 中读取 xml 文件

问题描述

我正在尝试从 XML 读取数据并在 SQL Server 中以表格形式显示结果.

I am trying to read data from XML and display the results in tabular for in SQL Server.

XML 数据:

<GradeSets>
    <GradeSet name="ALevel" descriptor="ALevelGrade" equivalence="1" allowedVariants="SplitGrades,FineGrades" va_multiplier="100">
        <Grade id="A*" points="140" success="true" />
        <Grade id="A" points="120" success="true" />
        <Grade id="B" points="100" success="true" />
        <Grade id="C" points="80" success="true" />
        <Grade id="D" points="60" success="true" />
        <Grade id="E" points="40" success="true" />
        <Grade id="U" points="0" success="false" />
        <Grade id="X" points="0" success="false" />
        <Grade id="Q" points="0" success="false">
            <Meta key="excludeStudent">true</Meta>
        </Grade>
        <GradeGroup id="A*>E">
            <Grade>A*</Grade>
            <Grade>A</Grade>
            <Grade>B</Grade>
            <Grade>C</Grade>
            <Grade>D</Grade>
            <Grade>E</Grade>
        </GradeGroup>
        <GradeGroup id="A*>C">
            <Grade>A*</Grade>
            <Grade>A</Grade>
            <Grade>B</Grade>
            <Grade>C</Grade>
        </GradeGroup>
        <GradeGroup id="A*>B">
            <Grade>A*</Grade>
            <Grade>A</Grade>
            <Grade>B</Grade>
        </GradeGroup>
    </GradeSet>
    <GradeSet name="ALevel-Double" descriptor="ALevelGrade" equivalence="2" va_multiplier="200">
        <Grade id="A*A*" points="280" success="true" />
        <Grade id="A*A" points="260" success="true" />
        <Grade id="AA" points="240" success="true" />
        <Grade id="AB" points="220" success="true" />
        <Grade id="BB" points="200" success="true" />
        <Grade id="BC" points="180" success="true" />
        <Grade id="CC" points="160" success="true" />
        <Grade id="CD" points="140" success="true" />
        <Grade id="DD" points="120" success="true" />
        <Grade id="DE" points="100" success="true" />
        <Grade id="EE" points="80" success="true" />
        <Grade id="U" points="0" success="false" />
        <Grade id="X" points="0" success="false" />
        <Grade id="Q" points="0" success="false">
            <Meta key="excludeStudent">true</Meta>
        </Grade>
        <GradeGroup id="A*A*>EE">
            <Grade>A*A*</Grade>
            <Grade>A*A</Grade>
            <Grade>AA</Grade>
            <Grade>AB</Grade>
            <Grade>BB</Grade>
            <Grade>BC</Grade>
            <Grade>CC</Grade>
            <Grade>CD</Grade>
            <Grade>DD</Grade>
            <Grade>DE</Grade>
            <Grade>EE</Grade>
        </GradeGroup>
        <GradeGroup id="A*A*>CC">
            <Grade>A*A*</Grade>
            <Grade>A*A</Grade>
            <Grade>AA</Grade>
            <Grade>AB</Grade>
            <Grade>BB</Grade>
            <Grade>BC</Grade>
            <Grade>CC</Grade>
        </GradeGroup>
        <GradeGroup id="A*A*>BB">
            <Grade>A*A*</Grade>
            <Grade>A*A</Grade>
            <Grade>AA</Grade>
            <Grade>AB</Grade>
            <Grade>BB</Grade>
        </GradeGroup>
    </GradeSet>
</GradeSets>

我写了这个查询，但我无法让它工作:

I wrote this query, but I cannot get it to work:

SELECT
   MY_XML.GradeSet.query('GradeSet').value('.', 'VARCHAR(20)') ,
   MY_XML.GradeSet.query('GradeGroup').value('.', 'VARCHAR(20)')
   
FROM (SELECT CAST(MY_XML AS xml)
      FROM OPENROWSET(BULK 'C:\Users\johno\OneDrive\Desktop\MyData\MyDataImport.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
      CROSS APPLY MY_XML.nodes('GradeSets/GradeSet') AS MY_XML (GradeSet);

电流输出

(No column name)    (No column name)
                     A*ABCDEA*ABCA*AB
                     A*A*A*AAAABBBBCCCCDD

预期输出公会

推荐答案

请尝试以下操作.如果它不是您所需要的，但它会给您一个良好的开端.

Please try the following. If it is not exactly what you need, but it will give you a good head start.

SQL

-- directly from the XML file as a virtual DB table on the file system
;WITH rs (xmldata) AS
(
   SELECT TRY_CAST(BulkColumn AS XML) AS BulkColumn 
   FROM OPENROWSET(BULK 'e:\Temp\JonWay.xml', SINGLE_BLOB) AS x
), cte AS
(
    SELECT a.value('@name', 'NVARCHAR(20)') AS [name]
       , a.value('@descriptor','NVARCHAR(50)') AS descriptor
       , a.value('@equivalence','INT') AS equivalence
       , a.value('@allowedVariants','NVARCHAR(100)') AS allowedVariants
       , a.value('@va_multiplier','INT') AS va_multiplier
       , b.value('@id','NVARCHAR(10)') AS id
       , b.value('@points','INT') AS points
       , b.value('@success','NVARCHAR(10)') AS success
       , c.value('(./text())[1]','NVARCHAR(10)') AS meta
       , c.value('@key','NVARCHAR(30)') AS [KEY]
       , d.value('@id','NVARCHAR(30)') AS id2
       , e.value('(./text())[1]','NVARCHAR(30)') AS Grade
    FROM rs AS tbl
       CROSS APPLY tbl.xmldata.nodes('/GradeSets/GradeSet[@name="ALevel"]') AS t1(a)
       OUTER APPLY t1.a.nodes('Grade') AS t2(b)
       OUTER APPLY t2.b.nodes('Meta') AS t3(c)
       OUTER APPLY t1.a.nodes('GradeGroup[@id="A*>E"]') AS t4(d)
       OUTER APPLY t4.d.nodes('Grade') AS t5(e)
), cte2 AS
(
    SELECT *
        , ROW_NUMBER() OVER (PARTITION BY id ORDER BY id, points) AS seq
    FROM cte
)
SELECT * FROM cte2
WHERE seq = 1
ORDER BY points DESC;

输出

+--------+-------------+-------------+------------------------+---------------+----+--------+---------+------+----------------+------+-------+-----+
|  name  | descriptor  | equivalence |    allowedVariants     | va_multiplier | id | points | success | meta |      KEY       | id2  | Grade | seq |
+--------+-------------+-------------+------------------------+---------------+----+--------+---------+------+----------------+------+-------+-----+
| ALevel | ALevelGrade |           1 | SplitGrades,FineGrades |           100 | A* |    140 | true    | NULL | NULL           | A*>E | A*    |   1 |
| ALevel | ALevelGrade |           1 | SplitGrades,FineGrades |           100 | A  |    120 | true    | NULL | NULL           | A*>E | A*    |   1 |
| ALevel | ALevelGrade |           1 | SplitGrades,FineGrades |           100 | B  |    100 | true    | NULL | NULL           | A*>E | A*    |   1 |
| ALevel | ALevelGrade |           1 | SplitGrades,FineGrades |           100 | C  |     80 | true    | NULL | NULL           | A*>E | A*    |   1 |
| ALevel | ALevelGrade |           1 | SplitGrades,FineGrades |           100 | D  |     60 | true    | NULL | NULL           | A*>E | A*    |   1 |
| ALevel | ALevelGrade |           1 | SplitGrades,FineGrades |           100 | E  |     40 | true    | NULL | NULL           | A*>E | A*    |   1 |
| ALevel | ALevelGrade |           1 | SplitGrades,FineGrades |           100 | Q  |      0 | false   | true | excludeStudent | A*>E | A*    |   1 |
| ALevel | ALevelGrade |           1 | SplitGrades,FineGrades |           100 | U  |      0 | false   | NULL | NULL           | A*>E | A*    |   1 |
| ALevel | ALevelGrade |           1 | SplitGrades,FineGrades |           100 | X  |      0 | false   | NULL | NULL           | A*>E | A*    |   1 |
+--------+-------------+-------------+------------------------+---------------+----+--------+---------+------+----------------+------+-------+-----+

这篇关于在 SQL Server 中读取 xml 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！