瓜哥有个需求场景,比如要按订单号查一批订单,数量2w个,如果用in拼接要写好长的语句,用string_split又限制长度8000。所以想想有什么什么好招。
顺嘴提一句,瓜哥就是MYFLL作者木瓜大侠
那就可以传入XML,让MSSQL把XML解析成表,然后连接查询返回结果。
先讲本质,打破神秘感。XML就是字符串,跟JSON一样,一种特殊规范的字符串,它跟HTML语言一样,是用<> 来表示键值对的结构,这个叫标签,比如最简单的结构:
<姓名>张三</姓名> //XML 标签名(key键) 姓名,标签值(value值) 张三
{"姓名":"张三"} //JSON
XML和JSON都是表示姓名:张三的键值对。
更专业的定义,请善用搜索引擎。
id | name |
---|---|
1 | 张三 |
2 | 李四 |
XML表示方法一:标签值法
<row>
<id>1</id><name>张三</name>
<id>2</id><name>李四</name>
</row>
XML表示方法二:标签行属性法
还有一种就是把每行的字段放在标签属性中
<row id="1" name="张三"></row>
<row id="2" name="李四"></row>
SELECT * FROM SPU FOR XML RAW,ELEMENTS
查询结果如下:
可以看到,每一行记录都是键值对的形式,然后被 row的键包着。
FOR XML RAW 表示 用RAW模式生成,这个参数默认是生成如下行属性的格式,不是我们想要的XML格式。
<row id="22" image="images/goods/20220909120216336481.jpg" spucode="100009002118 " goodname="家乐浓香鸡味调味料-1*20kg" goodintroduced="1112" gooddeail="111" typeid="1" />加个ELEMENTS参数就可以生成我们想要的XML格式了。
原来每一行是ROW标签,现在我们想换一个,只需要在RAW 后面加一个参数
SELECT * FROM SPU FOR XML RAW('item'),ELEMENTS
我们还可以将表名做为最外层的根结点
其实上面代码也可以写成如下:
SELECT * FROM SPU FOR XML path('item'),root('spu')
用path参数,就不用加ELEMENTS关键字了,少写一个是一个。
标签值法
方法1:
-- -- 也可以用varchar,与nvarchar 如果是有汉字要定义成nvarchar,不然会出错
Declare @cxml as xml
set @cxml='
<rows>
<row>
<id>1</id>
<name>张三</name>
</row>
<row>
<id>2</id>
<name>李四</name>
</row>
</rows>
'
DECLARE @xmlDoc integer
-- sp_xml_preparedocument存储过程用来创建XML结构
EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @cxml
-- OPENXML 参数2 代表是rows/row数据行所在路径,参数3:不能错,用的是标签值转换方式
-- with 语句定义数据类型
SELECT * FROM
OPENXML (@xmlDoc, 'rows/row', 2)
WITH
(id int ,
name varchar(50)
)
-- 用完移除xml变量
exec sp_xml_removedocument @xmlDoc
OPENXML (@xmlDoc, 'rows/row', 2)内的rows/row 结构是跟XML内容是对应的
方法二:
Declare @cxml as xml
set @cxml='
<rows>
<row>
<id>1</id>
<name>张三</name>
</row>
<row>
<id>2</id>
<name>李四</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)
标签行属性法
-- 也可以用varchar,与nvarchar 如果是有汉字要定义成nvarchar,不然会出错
Declare @cxml as xml
set @cxml='
<rows>
<row id="1" name="张三" />
<row id="2" name="李四" />
</rows>
'
DECLARE @xmlDoc integer
-- sp_xml_preparedocument存储过程用来创建XML结构
EXEC sp_xml_preparedocument @xmlDoc OUTPUT, @cxml
-- OPENXML 参数2 代表是rows/row数据行所在路径,参数3:不能错,用的是标签值转换方式
-- with 语句定义数据类型
SELECT * FROM
OPENXML (@xmlDoc, 'rows/row', 1)
WITH
(id int ,
name varchar(50)
)
-- 用完移除xml变量
exec sp_xml_removedocument @xmlDoc
方法二:
Declare @cxml as xml
set @cxml='
<rows>
<row id="1" name="张三" />
<row id="2" name="李四" />
</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) 相当于表名, 也可以把值赋给变量 @值=x.item.value('@id', 'int')
标签值和标签行属性XML的方法二差异在如下
//标签值法
x.item.value('id[1]','int')
x.item.value('name[1]', 'VARCHAR(100)')
//标签行属性法
x.item.value('@id', 'int') AS id,
x.item.value('@name', 'VARCHAR(100)') AS name
这是一种叫XQUERY的查询语法。
declare @XML xml = '
<ListOrderItem>
<OrderItem>
<Item>
<Seller>1</Seller>
</Item>
<Item>
<Seller>2</Seller>
</Item>
</OrderItem>
</ListOrderItem>'
-- 第二行数据
declare @I int = 1
select @XML.value('(ListOrderItem[1]/OrderItem[1]/Item[sql:variable("@I")]/Seller[1])[1]','VARCHAR(64)')
对了,忘记写VFP了,
1. 做存储过程,VFP发送SQLEXEC函数指令调用它。
2.做成T-SQL指令发送过去。
好了,今天的内容写完了,花的时间不少来验证。