Explain the purposes of OPENXML clause sql server stored procedure
OPENXML parses the XML data in SQL Server in an efficient manner. It’s primary ability is to insert XML data to the RDB. It is also possible to query the data by using OpenXML. The path of the XML element needs to be specified by using ‘xpath’.
The following is a procedure for retrieving xml data:
DECLARE @index int
DECLARE @xmlString varchar(8000)
SET @xmlString ='
Prasanth
9343463943/PhoneNo>
Laasya
9342673212
'
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 results the following:
15201 Prasanth 9343463943
15202 Laasya 9342673212
