SQL 2005 aduce imbunatatziri semnificative la manipularea XML si in sfirsit se poate renuntza la (oribilele si neperformantele) OPENXML si sp_xml_preparedocument. Incearca asa:

declare @x xml;

select @x = N'<boys>

  <boy FirstName="kuku" Counter="8" Year="7" />

  <boy FirstName="dfdfdf" Counter="0" Year="88" />

</boys>';

select t.boy.value('@FirstName', 'varchar(20)') as FirstName,

      t.boy.value('@Counter', 'int') as Counter,

      t.boy.value('@Year', 'int') as Year

      from @x.nodes('//boys/boy') t(boy)

Sau, pentru a obtine o performantza mai buna, declara schema datelor tale:

create xml schema collection [boys] as '<?xml version="1.0" encoding="utf-8"?>

<xs:schema

      attributeFormDefault="unqualified"

      elementFormDefault="qualified"

      xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:element name="boys">

    <xs:complexType>

      <xs:sequence>

        <xs:element maxOccurs="unbounded" name="boy">

          <xs:complexType>

            <xs:attribute name="FirstName" type="xs:string" use="required" />

            <xs:attribute name="Counter" type="xs:unsignedByte" use="required" />

            <xs:attribute name="Year" type="xs:unsignedByte" use="required" />

          </xs:complexType>

        </xs:element>

      </xs:sequence>

    </xs:complexType>

  </xs:element>

</xs:schema>';

go

declare @x xml(boys);

select @x = N'<boys>

  <boy FirstName="kuku" Counter="8" Year="7" />

  <boy FirstName="dfdfdf" Counter="0" Year="88" />

</boys>';

select t.boy.value('@FirstName', 'varchar(20)') as FirstName,

      t.boy.value('@Counter', 'int') as Counter,

      t.boy.value('@Year', 'int') as Year

      from @x.nodes('//boys/boy') t(boy);

P.S pentru a obtine schema, pur si simplu incarca fragmentul XML in Visual Studio 2005 si selecteaza 'XML/Create Schema'...