DECLARE @index int
DECLARE @xmlString varchar(8000)
SET @xmlString =
'<Persons>
<Person id="1">
<Name> name1 </Name>
<PhoneNo> 022-2366854 </PhoneNo>
</Person>
<Person id="2">
<Name> name2 </Name>
<PhoneNo> 011-33366854 </PhoneNo>
</Person>'
EXEC sp_xml_preparedocument @index OUTPUT, @xmlString
SELECT * FROM OPENXML(@index, 'Persons/Person')
WITH (id Varchar(10) , Name1 varchar(100) 'Name' , PhoneNo1 Varchar(50) 'PhoneNo')
EXEC sp_xml_removedocument @index
EXAMPLE 2
=====================
DECLARE @xmlDoc NVARCHAR(200)
DECLARE @handle INT
SET @xmlDoc = N'
<authors>
<au_id>
<au_id1>409</au_id1>
<au_id2>56</au_id2>
<au_id3>7008</au_id3>
</au_id>
<au_lname>Bennet</au_lname>
<au_fname>Abraham</au_fname>
</authors>
'
EXEC sp_xml_preparedocument @handle OUTPUT, @xmlDoc
SELECT * FROM OPENXML (@handle, '/authors',3) WITH
(au_id3 VARCHAR(20) 'au_id/au_id3' ,
au_lname NVARCHAR(20) ,
au_fname NVARCHAR(20)
)
EXEC sp_xml_removedocument @handle
OPUTPUT
------------------------------------
au_id3 | au_lname | au_fname
------------------------------------
7008 | Bennet | Abraham
