日期:2014-05-17 浏览次数:20572 次
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)