分解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 行受影响)

*/