定义XML字段
create table FormAnswers(
answerid int identity(1,1) not null,
files xml null
)
files字段是XML字段，可能为NULL，不为null的时候，结构如下：
<package/>
或者
<package>
<file id="1" name="附件1" description=""/>
</package>
假如我们想要查找所有附件个数大于1的数据，SQL如下：
select * from formanswers where convert(char(100),files.query('count(/package/file)'))>1
更多SQL对XML字段的操作可以参考文章 SQL对Xml字段的操作
select * from Answers where XmlFiles.value('count(/package/file)','int')>1
package的结构如下：
<package addnewattchment="1">
<file id="1" name="111111" version="" versionDate="" description="若有" type="2" fileCode="778EBD67-AE27-4450-9614-C8AF11168346" fileName="aaa.docx" options="2" projectID="74" code="" editdate="2018年11月12日 10:40:07" template="" disabled="" />
<file id="2" name="222" version="" versionDate="" description="若有" type="2" fileCode="E3EC30E4-BE95-4A95-BF6F-56C952F2B410" fileName="bbb.docx" options="2" projectID="74" code="" editdate="2018年11月12日 10:40:07" template="" disabled="" />
</package>
获取所有文件名为aaa的版本号:
SELECT ROW_NUMBER() OVER(partition BY ProjectID ORDER BY ORGRequestAnswerID ASC ) AS rid,a.ProjectID,
m.c.value('@name', 'varchar(max)') as name,
m.c.value('@version', 'varchar(max)') as versionNum
FROM dbo.ORGRequestAnswers as a outer apply a.PackageXml.nodes('package/file') as m(c) WHERE DeletedDate IS NULL AND ObjectType='request' AND m.c.value('@name', 'varchar(max)') ='aaa'
data的结构如下：
<data>
<question id="txtProjectName">19年1月测试</question>
<question id="txtBusiness">上海交通大学医学院附属第九人民医院</question>
<question id="txtStudyID">11111</question>
<question id="txtStore">口腔正畸科</question>
<question id="txtPI">测试</question>
<question id="txtTotalBudget">100</question>
<question id="txtUnplanedBudget">100</question>
<question id="txtDistributedBudget">100</question>
<question id="tblList">
<row id="1">
<td id="Name">a</td>
<td id="Money">100</td>
<td id="Number" />
<td id="CardNo" />
<td id="Remark" />
</row>
<row id="2">
<td id="Name">b</td>
<td id="Money">300</td>
<td id="Number" />
<td id="CardNo" />
<td id="Remark" />
</row>
<row id="3">
<td id="Name">c</td>
<td id="Money">3100</td>
<td id="Number" />
<td id="CardNo" />
<td id="Remark" />
</row>
</question>
<question id="txtTotalMoney">3500</question>
</data>
获取所有id为Money的值
SELECT T2.lin.value('(td[@id="Money"])[1]', 'int') AS moneyAcount
FROM (SELECT Result.query('data/question[@id="tblList"]') AS xmlSmartTable FROM dbo.BalanceRequestAnswers WHERE BalanceRequestID=(SELECT ObjectID FROM dbo.WorkflowInstances WHERE WorkflowInstanceID={0})) resultSmartTable
CROSS APPLY xmlSmartTable.nodes('/question/row') as T2(lin)
