VFP发送XML与MSSQL的互操作， 解决一个传大表查询的大大大问题

瓜哥有个需求场景，比如要按订单号查一批订单，数量2w个，如果用in拼接要写好长的语句，用string_split又限制长度8000。所以想想有什么什么好招。

瓜哥就是MYFLL作者木瓜大侠

那就可以传入XML，让MSSQL把XML解析成表，然后连接查询返回结果。

1. XML扫盲

先讲本质，打破神秘感。XML就是字符串，跟JSON一样，一种特殊规范的字符串，它跟HTML语言一样，是用<> 来表示键值对的结构，这个叫标签，比如最简单的结构：

<姓名>张三  //XML 标签名（key键） 姓名，标签值（value值） 张三 
{"姓名":"张三"}  //JSON
1
2

XML和JSON都是表示姓名：张三的键值对。

更专业的定义，请善用搜索引擎。

表的数据结构

**XML表示方法一：**标签值法

  1张三
  2李四

1
2
3
4

**XML表示方法二：**标签行属性法

还有一种就是把每行的字段放在标签属性中

1
2

2. 将MSSQL单表生成XML

SELECT * FROM SPU FOR XML RAW,ELEMENTS	
1

查询结果如下：

可以看到，每一行记录都是键值对的形式，然后被 row的键包着。

FOR XML RAW 表示 用RAW模式生成，这个参数默认是生成如下行属性的格式，不是我们想要的XML格式。

1

加个ELEMENTS参数就可以生成我们想要的XML格式了。

原来每一行是ROW标签，现在我们想换一个,只需要在RAW 后面加一个参数

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

我们还可以将表名做为最外层的根结点

其实上面代码也可以写成如下:

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

用path参数，就不用加ELEMENTS关键字了，少写一个是一个。

3. 将XML生成表

标签值法

方法1：

-- -- 也可以用varchar,与nvarchar 如果是有汉字要定义成nvarchar,不然会出错
Declare @cxml as xml
set @cxml='

 
   1
   张三
 
  
   2
   李四
 
 
'

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

OPENXML (@xmlDoc, ‘rows/row’, 2)内的rows/row 结构是跟XML内容是对应的

方法二：

Declare @cxml as  xml
set @cxml='

 
   1
   张三
 
  
   2
   李四
 
 
'
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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

标签行属性法

-- 也可以用varchar,与nvarchar 如果是有汉字要定义成nvarchar,不然会出错
Declare @cxml as  xml 
set @cxml='

 
 
 
'

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

方法二:

Declare @cxml as xml
set @cxml='

 
 
 
'

SELECT
		x.item.value('@id', 'int') AS id,
        x.item.value('@name', 'VARCHAR(100)') AS name
		FROM @cxml.nodes('//rows/row') AS x(item)
1
2
3
4
5
6
7
8
9
10
11
12

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
1
2
3
4
5
6
7

这是一种叫XQUERY的查询语法。

declare @XML xml = '

 
  
   1
  
  
   2
  
  
'
-- 第二行数据
declare @I int = 1
select @XML.value('(ListOrderItem[1]/OrderItem[1]/Item[sql:variable("@I")]/Seller[1])[1]','VARCHAR(64)')
1
2
3
4
5
6
7
8
9
10
11
12
13
14

对了，忘记写VFP了，

1. 做存储过程，VFP发送SQLEXEC函数指令调用它。

2. 做成T-SQL指令发送过去。

好了，今天的内容写完了，花的时间不少来验证。