SQL Server reads XML format (simulating dataset parameters)

I often want to use data set parameters for processing stored procedures, but SQL server does not provide such services.

Oracle seems to have, and this is the gap ~~~

There is no way for SQL Server to be used in this way.

Although SQL server does not explicitly provide a data set parameter, we can use some of its functions to implement it.

XML en ~ That is, it is a popular transmission format.

In fact, SQL Server has integrated the XML reading function.

We can use this feature to simulate the input of a set parameter. Is an XML format.

For example:

Declare @ SQL varchar (2000) -- this is a parameter passed in from the stored procedure.
 
Declare @ IDOC int
 
Set @ SQL ='
 
<Root>
 
<Student name = ''tom ''age = ''21''/>
 
<Student name = ''jerry ''age = ''22''/>
 
<Student name = ''join'' age = ''23''/>
 
</Root>
 
'
 
Exec sp_xml_preparedocument @ IDOC output, @ SQL -- create an XML handle ??

-- Query Result

 
Select * From openxml (@ IDOC, '/root/student', 1)
With (name varchar (10 ),
Age INT)
 
Exec sp_xml_removedocument @ IDOC -- clear the memory

Final Result

Name age

Tom 21

Jerry 22

Join 23

However, this has some disadvantages. the maximum length of the parameter here is 8000, that is, an error may occur if the length exceeds eight thousand.

 

PS: see othersCodeHow is it sent ?? No ~~