Gua Ge has a demand scenario. For example, to check a batch of orders according to the order number, the quantity is 2w. If you use in splicing, you need to write a long sentence, and use string_split to limit the length to 8000. So think about what you can do.

Guage is the author of MYFLL Papaya Hero

Then you can pass in XML, let MSSQL parse the XML into a table, and then connect the query to return the result.

1. XML literacy

Talk about the essence first, break the mystery. XML is a string. Like JSON, it is a special specification string. Like HTML, it uses <> to represent the structure of key-value pairs. This is called a label, such as the simplest structure:

<Name>Zhang San</Name>  //XML tag name (key key) name, tag value (value value) Zhang San 
{"Name":"Zhang San"}  //JSON

Both XML and JSON represent name: key-value pairs of Zhang San.

For more professional definitions, please make good use of search engines.

table data structure

id	name
1	Zhang San
2	Li Si

**XML representation method 1: **Tag value method

<row>
  <id>1</id><name>Zhang San</name>
  <id>2</id><name>Li Si</name>
</row>

**XML representation method 2: **Tag row attribute method

Another way is to put the fields of each row in the label attribute

<row id="1" name="Zhang San"></row>
<row id="2" name="Li Si"></row>

2. Generate XML from MSSQL single table

SELECT * FROM SPU FOR XML RAW,ELEMENTS	

The query results are as follows:

It can be seen that each row of records is in the form of a key-value pair, and is then wrapped by the key of the row.

FOR XML RAW indicates that it is generated in RAW mode. This parameter defaults to the format of generating the following attributes, not the XML format we want.

<row id="22" image="images/goods/20220909120216336481.jpg" spucode="100009002118                                " goodname="Knorr Chicken Flavor Seasoning-1*20kg" goodintroduced="1112" gooddeail="111" typeid="1" />

Adding an ELEMENTS parameter can generate the XML format we want.

It turns out that each row is a ROW label, now we want to change it, just add a parameter after RAW

SELECT * FROM SPU FOR XML RAW('item'),ELEMENTS	

We can also use the table name as the outermost root node

In fact, the above code can also be written as follows:

SELECT * FROM SPU FOR XML path('item'),root('spu')

With the path parameter, there is no need to add the ELEMENTS keyword, and one less is one.

3. Generate table from XML

tag value method

method 1:

-- -- can also be used varchar,and nvarchar If there are Chinese characters to be defined as nvarchar,otherwise it will go wrong
Declare @cxml as xml
set @cxml='
<rows>
 <row>
   <id>1</id>
   <name>Zhang San</name>
 </row>
  <row>
   <id>2</id>
   <name>Li Si</name>
 </row>
</rows> 
'

DECLARE @xmlDoc integer
-- sp_xml_preparedocument stored procedure to create XML structure
EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @cxml

-- OPENXML Parameter 2 stands for rows/row The path where the data row is located, parameter 3: can not be wrong, using the tag value conversion method
-- with Statements define data types

SELECT * FROM
OPENXML (@xmlDoc, 'rows/row', 2)
WITH
(id int ,
name varchar(50)
)

-- Remove when finished xml variable
exec sp_xml_removedocument @xmlDoc

The rows/row structure in OPENXML (@xmlDoc, 'rows/row', 2) corresponds to the XML content

Method Two:

Declare @cxml as  xml
set @cxml='
<rows>
 <row>
   <id>1</id>
   <name>Zhang San</name>
 </row>
  <row>
   <id>2</id>
   <name>Li Si</name>
 </row>
</rows> 
'
SELECT
		x.item.value('id[1]','int') as id ,
        x.item.value('name[1]', 'nVARCHAR(100)') as name
		FROM @cxml.nodes('//rows/row') AS x(item)

Label row attribute method

-- can also be used varchar,and nvarchar If there are Chinese characters to be defined as nvarchar,otherwise it will go wrong
Declare @cxml as  xml 
set @cxml='
<rows>
 <row id="1" name="Zhang San" />
 <row id="2" name="Li Si" />
</rows> 
'

DECLARE @xmlDoc integer
-- sp_xml_preparedocument stored procedure to create XML structure
EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @cxml

-- OPENXML Parameter 2 stands for rows/row The path where the data row is located, parameter 3: can not be wrong, using the tag value conversion method
-- with Statements define data types

SELECT * FROM
OPENXML (@xmlDoc, 'rows/row', 1)
WITH
(id int ,
name varchar(50)
)
-- Remove when finished xml variable
exec sp_xml_removedocument @xmlDoc

Method Two:

Declare @cxml as xml
set @cxml='
<rows>
 <row id="1" name="Zhang San" />
 <row id="2" name="Li Si" />
</rows> 
'

SELECT
		x.item.value('@id', 'int') AS id,
        x.item.value('@name', 'VARCHAR(100)') AS name
		FROM @cxml.nodes('//rows/row') AS x(item)

x(item) is equivalent to the table name, and you can also assign the value to the variable @value=x.item.value(‘@id’, ‘int’)

The difference between method 2 of tag value and tag line attribute XML is as follows

	//tag value method
	x.item.value('id[1]','int') 
    x.item.value('name[1]', 'VARCHAR(100)') 
    
	//Label row attribute method
	x.item.value('@id', 'int') AS id,
    x.item.value('@name', 'VARCHAR(100)') AS name

This is a query syntax called XQUERY.

declare @XML xml = '
<ListOrderItem>
 <OrderItem>
  <Item>
   <Seller>1</Seller>
  </Item>
  <Item>
   <Seller>2</Seller>
  </Item>
 </OrderItem> 
</ListOrderItem>'
-- second line of data
declare @I int = 1
select @XML.value('(ListOrderItem[1]/OrderItem[1]/Item[sql:variable("@I")]/Seller[1])[1]','VARCHAR(64)')

By the way, I forgot to write VFP,

1. As a stored procedure, VFP sends the SQLEXEC function instruction to call it.

2. Make a T-SQL command and send it over.

Well, today's content is finished, and it took a lot of time to verify.