VFP发送XML与MSSQL的互操作, 解决一个传大表查询的大大大问题
瓜哥有个需求场景,比如要按订单号查一批订单,数量2w个,如果用in拼接要写好长的语句,用string_split又限制长度8000。所以想想有什么什么好招。
顺嘴提一句,瓜哥就是MYFLL作者木瓜大侠
那就可以传入XML,让MSSQL把XML解析成表,然后连接查询返回结果。
1. 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>
2. 将MSSQL单表生成XML
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关键字了,少写一个是一个。
3. 将XML生成表
标签值法
方法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指令发送过去。
好了,今天的内容写完了,花的时间不少来验证。
相关文章
- 学生数据库管理系统
- SpringDataJpa 用MySQL语句怎么分页,spring全家桶SpringDataJpa 用MySQL语句怎么分页
- Docker创建MySQL容器模板命令
- Elasticsearch对应MySQL的对应关系
- 使用SpringDataJpa保存(save)报错误:SQL Error: 1062, SQLState: 23000 控制台会报:Duplicate entry ‘数‘ for key ‘PRIMA
- Navicat Premium 连接sqlserver数据库时提示安装Client失败,解决方案
- Mysql查询当前用户所有数据库语句(SHOW DATABASES)
- MySQL语句-查看当前数据库有哪些表(SHOW TABLES)
- MySQL5.0版本以上新增的 information_schema 数据库是什么?
- MariaDB数据库备份之逻辑备份
- MariaDB数据库创建用户
- MariaDB数据库给用户授权
- MariaDB数据库刷新权限表命令
- MariaDB数据库删除用户命令
- PhpStudy 2016搭建-sqli-libs靶场
- MySQL手动注入步骤
- Pikachu靶场-SQL注入-数字型注入(post)过关步骤
- Pikachu靶场-SQL注入-字符型注入(get)过关步骤
- 利用SQL注入漏洞实现MySQL数据库读写文件
- Kali-工具-sqlmap常见用法