分解XML

分解XML

DECLARE @xmlvar xml

set @xmlvar = '

<Data>

<Student id="s1">

<Name>Bugs Bunny</Name>

<Class>FFA</Class>

</Student>

<Student id="s2">

<Name>Road Runner</Name>

<Class>Sports</Class>

</Student>

<Student id="s3">

<Name>Wile E. Coyote</Name>

<Class>Science</Class>

</Student>

<Student id="s4">

<Name>Foghorn Leghorn</Name>

<Class>Self Defense</Class>

</Student>

</Data>'

--1

SELECT

A.student.value('@id','varchar(10)') AS id,

A.student.value('Name[1]','varchar(20)') AS Name,

A.student.value('Class[1]','varchar(20)') AS Class,

row_number() over (order by getdate()) AS ndx

FROM @xmlvar.nodes('/Data/Student') AS A(student)

--2

SELECT

A.student.value('@id','varchar(10)') AS id,

A.student.value('Name[1]','varchar(20)') AS Name,

A.student.value('Class[1]','varchar(20)') AS Class,

A.student.value('for $i in . return count($i/../*[.<<$i])+1','int') AS ndx

FROM @xmlvar.nodes('/Data/Student') AS A(student)

--3

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlvar

SELECT * ,row_number() over (order by getdate()) AS ndx

FROM OPENXML(@docHandle, N'/Data/Student/Class')

WITH (id char(2) '../@id',name varchar(20) '../Name',class varchar(20) '../Class' )

EXEC sp_xml_removedocument @docHandle

--结果

/*

id Name Class ndx

---------- -------------------- -------------------- -----------

s1 Bugs Bunny FFA 1

s2 Road Runner Sports 2

s3 Wile E. Coyote Science 3

s4 Foghorn Leghorn Self Defense 4

(4 行受影响)

id Name Class ndx

------- -------------------- -------------------- --------------------

s1 Bugs Bunny FFA 1

s2 Road Runner Sports 2

s3 Wile E. Coyote Science 3

s4 Foghorn Leghorn Self Defense 4

(4 行受影响)

id name class ndx

---- -------------------- -------------------- --------------------

s1 Bugs Bunny FFA 1

s2 Road Runner Sports 2

s3 Wile E. Coyote Science 3

s4 Foghorn Leghorn Self Defense 4

(4 行受影响)

*/