XML--将XML中数据提取出转换成表

DECLARE @xml XML
SET @xml='
<Students>
    <Student  id="1001">
        <name>aaa</name>
        <age>20</age>
        <birthday>1991-2-20</birthday>
    </Student>
    <Student  id="1002">
        <name>bbb</name>
        <age>21</age>
        <birthday>1990-2-20</birthday>
    </Student>    
</Students>
'
SELECT T.C1.value('../@id','VARCHAR(20)') AS StudentId,
T.C1.value('.','VARCHAR(20)') AS StudentName,
T.C1.value('../age[1]','VARCHAR(20)') AS StudentAge,
T.C1.value('../birthday[1]','datetime') AS StudentBirthDay
FROM @xml.nodes('/Students/Student/name') AS T(C1)

select  T.C.value('@id','varchar(max)') as StudentId,
        T.C.value('name[1]','varchar(max)') as StudentName,
        T.C.value('age[1]','varchar(max)') as StudentAge,
        T.C.value('birthday[1]','datetime') as StudentBirthDay
from    @xml.nodes('/Students/Student') as T ( C )

 

Note:

1>. 表示当前节点

2> ..表示上级节点

3> @表示属性

4> 第一个节点为[0]