读取XML为行记录

1 declare @x xml

2 set @x='<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2">

3 <title>Writing Secure Code</title>

4 <author>

5 <first-name>Michael</first-name>

6 <last-name>Howard</last-name>

7 </author>

8 <author>

9 <first-name>David</first-name>

10 <last-name>LeBlanc</last-name>

11 </author>

12 <PRice>39.99</price>

13</book>'

14

15

16

17SELECT nref.value('first-name[1]', 'nvarchar(50)') FirstName,

18 nref.value('last-name[1]', 'nvarchar(50)') LastName

19FROM @x.nodes('//author') AS R(nref)

20

21

结果为