Read xml file in SQL Server

Question

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

The XML data:

<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);

Current Output

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

Expected Output guild

What error are you getting? The column name in your query should be GradeSet rather than Grade to match the name: MY_XML.GradeSet.query(.... Also, note that the SQL Server service account will need read permissions to the folder/file. — Dan Guzman
– Dan Guzman, Commented Dec 20, 2020 at 11:34
Grade had no text properly in your question either, and there are multiple elements and you don't tell SQL Server which one you want. What are your expected results here? — Thom A
– Thom A ♦, Commented Dec 20, 2020 at 11:37
@DanGuzman this is the error message i got Msg 227, Level 15, State 1, Line 2 "Grade" is not a valid function, property, or field. — JonWay
– JonWay, Commented Dec 20, 2020 at 13:25
@JonWay, that error is because you have not changed Grade column name to GradeSet in the XML method as I suggested. This will fix the error but the results will concatenate all the text nodes under GradeGroup, which is probably not what you want. — Dan Guzman
– Dan Guzman, Commented Dec 20, 2020 at 13:30
@DanGuzman i have changed it as you wrote the result concatinated. How can i put them in a table. I have edited my question to reflect the new query — JonWay
– JonWay, Commented Dec 20, 2020 at 13:34

Yitzhak Khabinsky · Accepted Answer · 2020-12-20 17:08:15Z

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*&gt;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;

Output

+--------+-------------+-------------+------------------------+---------------+----+--------+---------+------+----------------+------+-------+-----+
|  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 |
+--------+-------------+-------------+------------------------+---------------+----+--------+---------+------+----------------+------+-------+-----+

Shivprasad Shelgavkar · Accepted Answer · 2021-10-22 18:18:48Z

    -- 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 GradeSet_Name
       , a.value('@descriptor','NVARCHAR(50)') AS GradeSet_descriptor
       , a.value('@equivalence','INT') AS GradeSet_equivalence
       , a.value('@allowedVariants','NVARCHAR(100)') AS GradeSet_allowedVariants
       , a.value('@va_multiplier','INT') AS GradeSet_va_multiplier
       , b.value('@id','NVARCHAR(10)') AS Grade_id
       , b.value('@points','INT') AS Grade_points
       , b.value('@success','NVARCHAR(10)') AS Grade_success
       , c.value('(./text())[1]','NVARCHAR(10)') AS Grade_meta
       , c.value('@key','NVARCHAR(30)') AS Grade_meta_KEY
       , NULL AS GradeGroupID
       , NULL AS GradeGroupID_Grade
       --, d.value('@id','NVARCHAR(30)') AS GradeGroupID
    --   , e.value('(./text())[1]','NVARCHAR(30)') AS GradeGroupID_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*&gt;E"]') AS t4(d)
       --OUTER APPLY t4.d.nodes('Grade') AS t5(e)

    UNION ALL

    SELECT 
        a.value('@name', 'NVARCHAR(20)') AS GradeSet_Name
       , a.value('@descriptor','NVARCHAR(50)') AS GradeSet_descriptor
       , a.value('@equivalence','INT') AS GradeSet_equivalence
       , a.value('@allowedVariants','NVARCHAR(100)') AS GradeSet_allowedVariants
       , a.value('@va_multiplier','INT') AS GradeSet_va_multiplier
       , NULL AS Grade_Id
       , NULL  AS Grade_points
       , NULL  AS Grade_success
       , NULL  AS Grade_meta
       , NULL  AS Grade_meta_KEY
       , d.value('@id','NVARCHAR(30)') AS GradeGroupID
       , e.value('(./text())[1]','NVARCHAR(30)') AS GradeGroupID_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*&gt;E"]') AS t4(d)
       OUTER APPLY t4.d.nodes('Grade') AS t5(e)

    UNION ALL

    SELECT 
        a.value('@name', 'NVARCHAR(20)') AS GradeSet_Name
       , a.value('@descriptor','NVARCHAR(50)') AS GradeSet_descriptor
       , a.value('@equivalence','INT') AS GradeSet_equivalence
       , a.value('@allowedVariants','NVARCHAR(100)') AS GradeSet_allowedVariants
       , a.value('@va_multiplier','INT') AS GradeSet_va_multiplier
       , NULL AS Grade_Id
       , NULL  AS Grade_points
       , NULL  AS Grade_success
       , NULL  AS Grade_meta
       , NULL  AS Grade_meta_KEY
       , d.value('@id','NVARCHAR(30)') AS GradeGroupID
       , e.value('(./text())[1]','NVARCHAR(30)') AS GradeGroupID_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*&gt;C"]') AS t4(d)
       OUTER APPLY t4.d.nodes('Grade') AS t5(e)

    UNION ALL

    SELECT 
        a.value('@name', 'NVARCHAR(20)') AS GradeSet_Name
       , a.value('@descriptor','NVARCHAR(50)') AS GradeSet_descriptor
       , a.value('@equivalence','INT') AS GradeSet_equivalence
       , a.value('@allowedVariants','NVARCHAR(100)') AS GradeSet_allowedVariants
       , a.value('@va_multiplier','INT') AS GradeSet_va_multiplier
       , NULL AS Grade_Id
       , NULL  AS Grade_points
       , NULL  AS Grade_success
       , NULL  AS Grade_meta
       , NULL  AS Grade_meta_KEY
       , d.value('@id','NVARCHAR(30)') AS GradeGroupID
       , e.value('(./text())[1]','NVARCHAR(30)') AS GradeGroupID_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*&gt;B"]') AS t4(d)
       OUTER APPLY t4.d.nodes('Grade') AS t5(e)

    UNION ALL

    SELECT 
        a.value('@name', 'NVARCHAR(20)') AS GradeSet_Name
       , a.value('@descriptor','NVARCHAR(50)') AS GradeSet_descriptor
       , a.value('@equivalence','INT') AS GradeSet_equivalence
       , a.value('@allowedVariants','NVARCHAR(100)') AS GradeSet_allowedVariants
       , a.value('@va_multiplier','INT') AS GradeSet_va_multiplier
       , b.value('@id','NVARCHAR(10)') AS Grade_id
       , b.value('@points','INT') AS Grade_points
       , b.value('@success','NVARCHAR(10)') AS Grade_success
       , c.value('(./text())[1]','NVARCHAR(10)') AS Grade_meta
       , c.value('@key','NVARCHAR(30)') AS Grade_meta_KEY
       , NULL AS GradeGroupID
       , NULL AS GradeGroupID_Grade
       --, d.value('@id','NVARCHAR(30)') AS GradeGroupID
    --   , e.value('(./text())[1]','NVARCHAR(30)') AS GradeGroupID_Grade
    FROM rs AS tbl
       CROSS APPLY tbl.xmldata.nodes('/GradeSets/GradeSet[@name="ALevel-Double"]') AS t1(a)
       OUTER APPLY t1.a.nodes('Grade') AS t2(b)
       OUTER APPLY t2.b.nodes('Meta') AS t3(c)

    UNION ALL

    SELECT 
        a.value('@name', 'NVARCHAR(20)') AS GradeSet_Name
       , a.value('@descriptor','NVARCHAR(50)') AS GradeSet_descriptor
       , a.value('@equivalence','INT') AS GradeSet_equivalence
       , a.value('@allowedVariants','NVARCHAR(100)') AS GradeSet_allowedVariants
       , a.value('@va_multiplier','INT') AS GradeSet_va_multiplier
       , NULL AS Grade_Id
       , NULL  AS Grade_points
       , NULL  AS Grade_success
       , NULL  AS Grade_meta
       , NULL  AS Grade_meta_KEY
       , d.value('@id','NVARCHAR(30)') AS GradeGroupID
       , e.value('(./text())[1]','NVARCHAR(30)') AS GradeGroupID_Grade
    FROM rs AS tbl
       CROSS APPLY tbl.xmldata.nodes('/GradeSets/GradeSet[@name="ALevel-Double"]') 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*A*&gt;EE"]') AS t4(d)
       OUTER APPLY t4.d.nodes('Grade') AS t5(e)

    UNION ALL

    SELECT 
        a.value('@name', 'NVARCHAR(20)') AS GradeSet_Name
       , a.value('@descriptor','NVARCHAR(50)') AS GradeSet_descriptor
       , a.value('@equivalence','INT') AS GradeSet_equivalence
       , a.value('@allowedVariants','NVARCHAR(100)') AS GradeSet_allowedVariants
       , a.value('@va_multiplier','INT') AS GradeSet_va_multiplier
       , NULL AS Grade_Id
       , NULL  AS Grade_points
       , NULL  AS Grade_success
       , NULL  AS Grade_meta
       , NULL  AS Grade_meta_KEY
       , d.value('@id','NVARCHAR(30)') AS GradeGroupID
       , e.value('(./text())[1]','NVARCHAR(30)') AS GradeGroupID_Grade
    FROM rs AS tbl
       CROSS APPLY tbl.xmldata.nodes('/GradeSets/GradeSet[@name="ALevel-Double"]') 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*A*&gt;CC"]') AS t4(d)
       OUTER APPLY t4.d.nodes('Grade') AS t5(e)

    UNION ALL

    SELECT 
        a.value('@name', 'NVARCHAR(20)') AS GradeSet_Name
       , a.value('@descriptor','NVARCHAR(50)') AS GradeSet_descriptor
       , a.value('@equivalence','INT') AS GradeSet_equivalence
       , a.value('@allowedVariants','NVARCHAR(100)') AS GradeSet_allowedVariants
       , a.value('@va_multiplier','INT') AS GradeSet_va_multiplier
       , NULL AS Grade_Id
       , NULL  AS Grade_points
       , NULL  AS Grade_success
       , NULL  AS Grade_meta
       , NULL  AS Grade_meta_KEY
       , d.value('@id','NVARCHAR(30)') AS GradeGroupID
       , e.value('(./text())[1]','NVARCHAR(30)') AS GradeGroupID_Grade
    FROM rs AS tbl
       CROSS APPLY tbl.xmldata.nodes('/GradeSets/GradeSet[@name="ALevel-Double"]') 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*A*&gt;BB"]') AS t4(d)
       OUTER APPLY t4.d.nodes('Grade') AS t5(e)
)
SELECT * FROM cte;

Output 1

Output 2

Output 3

Collectives™ on Stack Overflow

Read xml file in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related