问题描述
我正在尝试从 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 | +--------+-------------+-------------+------------------------+---------------+----+--------+---------+------+----------------+------+-------+-----+