在 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 |
+--------+-------------+-------------+------------------------+---------------+----+--------+---------+------+----------------+------+-------+-----+
相关文章