sql xml 查询问题,求大虾给解决方案！！！
有这么一列数据 :
id int content varchar(8000)存放的是xml数据格式的数据，如下：
1 <root><pati_name>张三</pati_name><pati_sex>男</pati_sex> <in_patient_no>682977</in_patient_no><bed>001</bed><apply_time>2013-08-06T9:56:06.913</apply_time></root>
2 <root><pati_name>李四</pati_name><pati_sex>女</pati_sex> <in_patient_no>682977</in_patient_no><bed>002</bed><apply_time>2013-08-07T10:56:06.913</apply_time></root>
3 ...
我如何取里边的一个节点做查询呢？或者是转化成xml格式的字段进行查询 SQL XML
[解决办法]
CREATE TABLE #temp(id INT, content VARCHAR(8000))
INSERT #temp
SELECT 1, '<root><pati_name>张三</pati_name><pati_sex>男</pati_sex> <in_patient_no>682977</in_patient_no><bed>001</bed><apply_time>2013-08-06T9:56:06.913</apply_time></root>' UNION ALL
SELECT 2, '<root><pati_name>李四</pati_name><pati_sex>女</pati_sex> <in_patient_no>682977</in_patient_no><bed>002</bed><apply_time>2013-08-07T10:56:06.913</apply_time></root>'
SELECT * FROM
(SELECT id, content=CAST(content AS XML) FROM #temp) a
CROSS APPLY
(
SELECT
pati_name = T.c.value('(./pati_name/text())[1]', 'nvarchar(max)'),
pati_sex = T.c.value('(./pati_sex/text())[1]', 'nchar(1)'),
in_patient_no = T.c.value('(./in_patient_no/text())[1]', 'int'),
bed = T.c.value('(./bed/text())[1]', 'varchar(10)'),
apply_time = T.c.value('(./apply_time/text())[1]', 'varchar(100)')
FROM a.content.nodes('root') T(c)
) b
--对于DATETIME类型，测试了一下
apply_time = CONVERT(DATETIME, T.c.value('(./apply_time/text())[1]', 'varchar(100)'), 127)
SELECT CONVERT(DATETIME, '2013-08-07T10:56:06.913', 127) --成功
SELECT CONVERT(DATETIME, '2013-08-06T9:56:06.913', 126) --失败，原因是因为日期格式不对，9前面少了个0.
具体参考：http://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=ZH-CN&k=k(CONVERT_TSQL);k(SQL11.SWB.TSQLRESULTS.F1);k(SQL11.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true
