[BTS] Use TSQL to query a XML field in SQLServer

When deploy BizTalk application. I often save XML message to database. 

 

When display some field in XML, I need following SQL to query XML field. 

 

 

starttime,
select 

 

convert(xml,RequestXML).value('(//GENERALDATA/SERIAL_NO)[1]','varchar(30)') SERIAL_NO
from BizTalkTask
where interfaceid=10001
order by id desc


select 
starttime,
convert(xml,RequestXML).query('//GENERALDATA/SERIAL_NO') XmlNode
from BizTalkTask
where interfaceid=10001
order by id desc

  


An anther sample:

SELECT x.requestpayload.value('declare namespace s="http://blah.ca/api"; (/s:validate-student-request/s:student-id)[1]', 'int') as studentidFROM xoutput x

 <XmlColumn>.value('...namespace....; (...xPath...)[1]', 'Type') as ColumnAlias

 

Key : BizTalk Guideline 入门 教程

徐中

北京钛谷诚泽网络通讯科技有限公司

 

 

 

 

posted on 2012-12-03 16:37  徐中  阅读(358)  评论(0编辑  收藏  举报

导航