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,
-
As a stored procedure, VFP sends the SQLEXEC function instruction to call it.
-
Make a T-SQL command and send it over.
Well, today's content is finished, and it took a lot of time to verify.