SQL Database Concepts Question: Download Questions PDF
Do you know what is OPENXML in SQL Server?
Answer:
-OPENXML provides an easy way to use an XML document as a data-source for your procedures.
-OPENXML data can be manipulated the same way we deal with database tables by treating xml tags in the form of columns and the value in the form of rows.
-By using OPENXML Data can be inserted or updated very quickly without multiple trips to the database.
-Example:
DECLARE @count int
DECLARE @xml varchar(5000)
SET @xml ='<Employees>
<Employee id="1">
<Name>DEF</Name>
<Employee >1234</ Employee >
</Employee >
<Employee id="2">
<Name>ABC</Name>
<PhoneNo>2211</PhoneNo>
</Employee >
</Employees>'
EXEC sp_xml_preparedocument @count OUTPUT, @xml
SELECT *
FROM OPENXML (@count, Employees/Employee')
WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')
EXEC sp_xml_removedocument @index
It will give following result.
1 DEF 1234
2 ABC 2211
-OPENXML data can be manipulated the same way we deal with database tables by treating xml tags in the form of columns and the value in the form of rows.
-By using OPENXML Data can be inserted or updated very quickly without multiple trips to the database.
-Example:
DECLARE @count int
DECLARE @xml varchar(5000)
SET @xml ='<Employees>
<Employee id="1">
<Name>DEF</Name>
<Employee >1234</ Employee >
</Employee >
<Employee id="2">
<Name>ABC</Name>
<PhoneNo>2211</PhoneNo>
</Employee >
</Employees>'
EXEC sp_xml_preparedocument @count OUTPUT, @xml
SELECT *
FROM OPENXML (@count, Employees/Employee')
WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')
EXEC sp_xml_removedocument @index
It will give following result.
1 DEF 1234
2 ABC 2211
Download Basic SQL Server Interview Questions And Answers
PDF
| Previous Question | Next Question |
| What is failover clustering overview? | Do you know what are the ways available in SQL Server to execute SQL statements? |