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