SQL解析XML文件
2024-08-27 22:24:29
DECLARE @idoc int
DECLARE @doc varchar(max)
SET @doc ='<?xml version="1.0" encoding="gb2312" ?><NewDataSet>
<SQLDataSet>
<id>1</id>
<QCCheckControlItemId>QCCL0000000H</QCCheckControlItemId>
<ObjType>首件</ObjType>
<Project>尺寸</Project>
<Specifications>12.25±0.2</Specifications>
<Sample1>234</Sample1>
<Sample2>234</Sample2>
<Sample3>234</Sample3>
<Sample4>43</Sample4>
<Sample5 />
<Judgement>NG</Judgement>
</SQLDataSet>
<SQLDataSet>
<id>2</id>
<QCCheckControlItemId>QCCL0000000I</QCCheckControlItemId>
<ObjType>首件</ObjType>
<Project>尺寸</Project>
<Specifications>52±0.2</Specifications>
<Sample1>234</Sample1>
<Sample2>234</Sample2>
<Sample3>2</Sample3>
<Sample4>234</Sample4>
<Sample5 />
<Judgement>NG</Judgement>
</SQLDataSet>
<SQLDataSet>
<id>3</id>
<QCCheckControlItemId>QCCL0000000P</QCCheckControlItemId>
<ObjType>首件</ObjType>
<Project>尺寸</Project>
<Specifications>12±2</Specifications>
<Sample1>324</Sample1>
<Sample2>234</Sample2>
<Sample3>234</Sample3>
<Sample4>234</Sample4>
<Sample5 />
<Judgement>NG</Judgement>
</SQLDataSet>
<SQLDataSet>
<id>4</id>
<QCCheckControlItemId>QCCL0000001D</QCCheckControlItemId>
<ObjType>首件</ObjType>
<Project>尺寸</Project>
<Specifications>13.12±2</Specifications>
<Sample1>23</Sample1>
<Sample2>324</Sample2>
<Sample3>234</Sample3>
<Sample4>234</Sample4>
<Sample5 />
<Judgement>NG</Judgement>
</SQLDataSet>
<SQLDataSet>
<id>5</id>
<QCCheckControlItemId>QCCL0000001E</QCCheckControlItemId>
<ObjType>首件</ObjType>
<Project>尺寸</Project>
<Specifications>13.79±0.2</Specifications>
<Sample1>324</Sample1>
<Sample2>324</Sample2>
<Sample3>342</Sample3>
<Sample4>324</Sample4>
<Sample5 />
<Judgement>NG</Judgement>
</SQLDataSet>
<SQLDataSet>
<id>6</id>
<QCCheckControlItemId>QCCL0000001F</QCCheckControlItemId>
<ObjType>首件</ObjType>
<Project>尺寸</Project>
<Specifications>16.05±0.2</Specifications>
<Sample1 />
<Sample2 />
<Sample3 />
<Sample4 />
<Sample5 />
<Judgement />
</SQLDataSet>
<SQLDataSet>
<id>7</id>
<QCCheckControlItemId>QCCL0000001G</QCCheckControlItemId>
<ObjType>首件</ObjType>
<Project>尺寸</Project>
<Specifications>4.95±0.2</Specifications>
<Sample1 />
<Sample2 />
<Sample3 />
<Sample4 />
<Sample5 />
<Judgement />
</SQLDataSet>
<SQLDataSet>
<id>8</id>
<QCCheckControlItemId>QCCL0000000X</QCCheckControlItemId>
<ObjType>首件</ObjType>
<Project>功能</Project>
<Sample1 />
<Sample2 />
<Sample3 />
<Sample4 />
<Sample5 />
<Judgement />
</SQLDataSet>
<SQLDataSet>
<id>9</id>
<QCCheckControlItemId>QCCL0000000W</QCCheckControlItemId>
<ObjType>首件</ObjType>
<Project>结构</Project>
<Sample1 />
<Sample2 />
<Sample3 />
<Sample4 />
<Sample5 />
<Judgement />
</SQLDataSet>
<SQLDataSet>
<id>10</id>
<QCCheckControlItemId>QCCL0000000Y</QCCheckControlItemId>
<ObjType>首件</ObjType>
<Project>实配</Project>
<Sample1 />
<Sample2 />
<Sample3 />
<Sample4 />
<Sample5 />
<Judgement />
</SQLDataSet>
<SQLDataSet>
<id>11</id>
<QCCheckControlItemId>QCCL0000000V</QCCheckControlItemId>
<ObjType>首件</ObjType>
<Project>外观</Project>
<Sample1 />
<Sample2 />
<Sample3 />
<Sample4 />
<Sample5 />
<Judgement />
</SQLDataSet>
</NewDataSet>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/NewDataSet/SQLDataSet',2)
WITH (id char(12) 'id',
QCCheckControlItemId char(12) 'QCCheckControlItemId',
ObjType nvarchar(100) 'ObjType',
Project nvarchar(100) 'Project',
Specifications nvarchar(100) 'Specifications',
Sample1 float 'Sample1',
Sample2 float 'Sample2',
Sample3 float 'Sample3',
Sample4 float 'Sample4',
Sample5 float 'Sample5',
Judgement varchar(10) 'Judgement' ) exec sp_xml_removedocument @idoc
长度一定要够不然会报错：例如
XML 文本 " <Specifications>12±2</Specifications" 附近的行号 33 处出现 XML 分析错误 0xc00ce55f。
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
错误说明是 'End 元素缺少 '>' 字符。'。
Msg 8179, Level 16, State 5, Line 147
找不到句柄为 0 的预定义语句。
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: 为参数 1 提供的值无效。
编码结构不对：例如
XML 文本 " <ObjType>" 附近的行号 5 处出现 XML 分析错误 0xc00ce508。
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
错误说明是 '文本内容中发现无效字符。'。
Msg 8179, Level 16, State 5, Line 147
找不到句柄为 0 的预定义语句。
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: 为参数 1 提供的值无效。
最新文章
- Android笔记：异步消息处理
- [转]Java反射之如何判断类或变量、方法的修饰符(Modifier解析)
- 判断日期是否符合yyyy-mm格式
- Can't find bundle for base name ClientMessages, locale zh_CN
- HTML+CSS基础学习笔记（5）
- github 使用方法总结 还有一部分不太懂
- H.数7（模拟）
- 投资新兴市场和细分市场 good
- java--照片和BYTE这些东西阵列
- The most orzed and orzing man
- Chef 自动化运维：Chef 的安装
- 使用svn 的解决 处理svn状态冲突
- 【转】GAMITBLOBK中固定解、浮点解、约束解、松弛解等解类型解释
- Andy's First Dictionary---set，stringstream
- LeetCode哈希表
- BZOJ3110 [Zjoi2013]K大数查询 树套树 线段树 整体二分 树状数组
- Django之如何预防csrf功能的方式 form提交与ajax提交
- 网络编程 tcp(一)
- SpringAOP实战应用
- springMVC工程使用jreloader实现热部署
热门文章
- RubyMine2017破解
- Unity 3D本地公布WebPlayer版时&quot;Failed to download data file&quot;解决方式
- find the safest road HDU杭电1596【Dijkstra || SPFA】
- 利用 border 实现的图片选区效果，只需一层图一蒙层
- [NOI2002] Savage 解题报告(扩展欧几里得)
- MetaSploit攻击实例讲解------终端下PostgreSQL数据库的使用（包括kali linux 2016.2(rolling) 和 BT5）
- SharePoint 修改完或制作完一定要发布
- Angular2/Ionic2集成Promact/md2.md
- PostgreSQL Replication之第九章 与pgpool一起工作（5）
- Oracle的Clob转换类型