Appear in Online Interview to test your skills and be ready for job!
Twitter Twitter LinkedIn YouTube Google
Be Interview ready ! | Search
Submit content and win cash & prizes monthly.
Winners

Win Prizes

Announcements
Like us on Facebook
Sponsor
Top Articles Author
Thu, 24-Apr-2014 Authors
Goud.Kv
280
All Time Authors
Rajkatie
20480
Vishal@Neeraj
19600
Bharathi Cherukuri
18040

Latest members | More ...


(Statistics delayed by 5 minutes)

What is OPENXML in SQL Server?

 Posted by Raja on 12/14/2008 | Category: Sql Server Interview questions | Views: 14412
Answer:

OPENXML can parse the xml data in SQL server very efficiently in SQL Server. OpenXML primarily gives the ability to insert XML data to the relational database, however we can query the data too using OpenXML. We need to specify the path of the xml element using xpath.

Syntax:

DECLARE @index int

DECLARE @xmlString varchar(8000)
SET @xmlString ='<Persons>
<Person id="1">
<Name>Mohan</Name>
<PhoneNo>34343</PhoneNo>
</Person>
<Person id="2">
<Name>Sita</Name>
<PhoneNo>23432</PhoneNo>
</Person>
</Persons>'

EXEC sp_xml_preparedocument @index OUTPUT, @xmlString

SELECT *
FROM OPENXML (@index, 'Persons/Person')
WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')

EXEC sp_xml_removedocument @index


The above code snippet will give following result.
---------------------------------
1 Mohan 34343
2 Sita 23432
---------------------------------


Asked In: Many Interviews | Alert Moderator 

Found interesting? Add this to:




Comments or Responses

Login to post response.

Comments / Responses
Select text & click toolbar to format. Formatting appears in viewmode only. HTML Tags are not allowed.
Bold Italic Underline Paragraph Title Code  Link 
 Wait ... Processing ..... please wait.