定義XML字段
create table FormAnswers(
answerid int identity(1,1) not null,
files xml null
)
files字段是XML字段歌殃，可能為NULL舆吮，不為null的時(shí)候外莲，結(jié)構(gòu)如下：
<package/>
或者
<package>
<file id="1" name="附件1" description=""/>
</package>
假如我們想要查找所有附件個(gè)數(shù)大于1的數(shù)據(jù)，SQL如下：
select * from formanswers where convert(char(100),files.query('count(/package/file)'))>1
更多SQL對(duì)XML字段的操作可以參考文章 SQL對(duì)Xml字段的操作
select * from Answers where XmlFiles.value('count(/package/file)','int')>1
package的結(jié)構(gòu)如下：
<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的版本號(hào):
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的結(jié)構(gòu)如下：
<data>
<question id="txtProjectName">19年1月測(cè)試</question>
<question id="txtBusiness">上海交通大學(xué)醫(yī)學(xué)院附屬第九人民醫(yī)院</question>
<question id="txtStudyID">11111</question>
<question id="txtStore">口腔正畸科</question>
<question id="txtPI">測(cè)試</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)
