SQL Server reads XML format (simulating dataset parameters)

Source: Internet
Author: User

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 ~~

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.