如何从SQL Server中的XML列中读取 [英] How to read from the XML column in SQL Server

问题描述

我需要以格式获得结果



HeadId MinExp maxEmp Rate

9 4 5 45

29 6 8 76

等等..



来自以下存储的XML在sql server table'列中命名为Configuration，其类型为XML

Hi,
I need to get the result in the format

HeadId MinExp maxEmp Rate
9 4 5 45
29 6 8 76
and so on..

from the following XML stored in sql server table' column named "Configuration" and whose type is "XML"

<PARAMS>
<Experience HeadId="9" MinExp="4" MaxExp="5" Rate="45" />
<Experience HeadId="29" MinExp="6" MaxExp="8" Rate="76" />
<Experience HeadId="31" MinExp="9" MaxExp="12" Rate="90" />
<Experience HeadId="32" MinExp="13" MaxExp="18" Rate="100" />
<Experience HeadId="33" MinExp="19" MaxExp="50" Rate="200" />
</PARAMS>

请帮助我。

Please help me.

推荐答案

这是一个绝对有帮助的解决方案： -

Here is a solution which will be definitely of help to you :-

declare @val xml
set @val = '<PARAMS>
			<Experience HeadId="9" MinExp="4" MaxExp="5" Rate="45" />
			<Experience HeadId="29" MinExp="6" MaxExp="8" Rate="76" />
			<Experience HeadId="31" MinExp="9" MaxExp="12" Rate="90" />
			<Experience HeadId="32" MinExp="13" MaxExp="18" Rate="100" />
			<Experience HeadId="33" MinExp="19" MaxExp="50" Rate="200" />
		</PARAMS>';

SELECT  x.node.value('(@HeadId)[1]', 'int') As HeadId,
        x.node.value('(@MinExp)[1]', 'int') AS MinExp,
        x.node.value('(@MaxExp)[1]', 'int') AS MaxExp,
        x.node.value('(@Rate)[1]', 'int') AS Rate
FROM    @val.nodes('/PARAMS/Experience') AS x(node)

这里@val是xml类型变量，它为我保存xml。

我们可以使用任何xml类型的列来代替这个变量来获取它的值作为要求。

Here @val is the xml type variable which holds the xml for me.
We can use any xml type column in place of this variable to get the values from it as requirement.

检查此linke与您的要求类似



http://stackoverflow.com/questions/14418256/extracting-data-in-a-record-from-xml-in-sql-server-栏 [ ^ ]

Check this linke its similar to you requirement

http://stackoverflow.com/questions/14418256/extracting-data-in-a-record-from-xml-in-sql-server-column[^]

这篇关于如何从SQL Server中的XML列中读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！