使用for xml子句从xmlstring检索列值 [英] To retrieve column values from an xmlstring using for xml clause
本文介绍了使用for xml子句从xmlstring检索列值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
嗨..
在我们的数据库表中,一列存储为xml string.which如下所示。
< DocumentElement >
< PartInfo >
< ID > ; 0 < / ID >
< 名称 / >
< PartNo > 0 < / PartNo >
< SerialNo > 1 < / SerialNo >
< 参数 > 0 < /参数 >
< InstalledDate > 2013-01 -15T00:00:00 + 05:30 < / InstalledDate >
< InstalledTill > 2013-01-25T00:00:00 + 05:30 < / InstalledTill >
< ; / PartInfo >
< / DocumentElement >
i想得到这个字符串的列值。例如我必须得到安装日期列的值2013-01-15T00:00:00 + 05.Pls告诉我怎么能我使用forxml条款获取此信息。请帮助我,我急需...请注意为什么......提前谢谢..
swathi。
解决方案
假设您的表是MyXMLData
且列名是XmlDataColumn
SELECT [XmlDataColumn] .query(' .// InstalledDate')。 value(' 。',' < span class =code-string> VARCHAR(MAX)')
FROM [MyXMLData]
关于您的意见,请检查以下查询
SELECT [XmlDataColumn] .value(' (// ID)[1]',< span class =code-string>' VARCHAR(MAX)') AS ID ,[XmlDataColumn] .value(' (// PartNo)[1]', VARCHAR(MAX)') AS PartNo
FROM [MyXMLData]
WHERE [XmlDataColumn] .value(' (// parent :: node()/ InstalledDate)[1]',' VARCHAR (MAX)')= ' 2013-01-15T00:00:00 + 05:30'< /跨度>
Hi..
In our database table one column is stored as an xml string.which is shown below.
<DocumentElement>
<PartInfo>
<ID>0</ID>
<Name />
<PartNo>0</PartNo>
<SerialNo>1</SerialNo>
<Parameter>0</Parameter>
<InstalledDate>2013-01-15T00:00:00+05:30</InstalledDate>
<InstalledTill>2013-01-25T00:00:00+05:30</InstalledTill>
</PartInfo>
</DocumentElement>
i want to get column values of this string.For example i have to get the value 2013-01-15T00:00:00+05 of installed Date column.Pls tell me how can i obtain this using forxml clause..Help me please i want it urgently..Thats why..Thanks in advance..
swathi.
解决方案
Suppose you table isMyXMLData
and the column name isXmlDataColumn
SELECT [XmlDataColumn].query('.//InstalledDate').value('.','VARCHAR(MAX)') FROM [MyXMLData]
With regarding your comments, please check following query
SELECT [XmlDataColumn].value('(//ID)[1]', 'VARCHAR(MAX)') AS ID, [XmlDataColumn].value('(//PartNo)[1]', 'VARCHAR(MAX)') AS PartNo FROM [MyXMLData] WHERE [XmlDataColumn].value('(//parent::node()/InstalledDate)[1]', 'VARCHAR(MAX)') ='2013-01-15T00:00:00+05:30'
这篇关于使用for xml子句从xmlstring检索列值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文