社区首页 >专栏 >VFP发送XML与MSSQL的互操作，解决一个传大表查询的大大大问题

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

加菲猫的VFP

发布于 2023-01-03 08:47:32

79700

代码可运行

文章被收录于专栏：加菲猫的VFP加菲猫的VFP

运行总次数：0

代码可运行

瓜哥有个需求场景，比如要按订单号查一批订单，数量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)')