[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

 

Key: BizTalk Guideline getting started

Xuzhong

Beijing titanu chengze Network Communication Technology Co., Ltd.