如何阅读SQL Server 2008中的XML列？(How to read XML column

我从来没有在SQL Server 2008中使用XML，我需要提取的字段列表到一个变量表你怎么办呢？

既然我有一个名为XMLDATA列在一个表XMLMain看起来像类似下面我怎么提取SQL字段列表？

！[在这里输入的形象描述] [1]

<?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:SampleForm:-myXSD-2014-03-29T09-41-23" solutionVersion="1.0.0.18" productVersion="15.0.0.0" PIVersion="1.0.0.0" href="http://bipc20/sites/team-1303/FormServerTemplates/SampleForm.xsn"?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.4"?>
<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2014-03-29T09:41:23" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">
<my:field1>1</my:field1>
<my:field2>2</my:field2>
<my:field3>true</my:field3>
<my:field4 xsi:nil="true" />
<my:field5 xsi:nil="true" />
<my:field6>4</my:field6>
<my:FormName>2014-04-01T15:11:47</my:FormName>
<my:Repeating>hi</my:Repeating>
<my:Repeating xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2014-03-29T09:41:23">hello</my:Repeating>
<my:Repeating xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2014-03-29T09:41:23">how are  you?</my:Repeating>
</my:myFields>

我想提取重复字段作为逗号分隔像前的价值。 在重复我们有三个值（嗨，你好，你怎么样？）

谁能帮帮我吗。？

Answer 1:

with xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2014-03-29T09:41:23' as my)
select M.XMLData.value('(/my:myFields/my:field1/text())[1]', 'int') as field1,
       M.XMLData.value('(/my:myFields/my:field2/text())[1]', 'int') as field2,
       M.XMLData.value('(/my:myFields/my:field3/text())[1]', 'bit') as field3,
       M.XMLData.value('(/my:myFields/my:FormName/text())[1]', 'datetime') as FormName,
       (
         select ','+R.X.value('text()[1]', 'nvarchar(max)')
         from M.XMLData.nodes('/my:myFields/my:Repeating') as R(X)
         for xml path(''), type
       ).value('substring(text()[1], 2)', 'nvarchar(max)') as Repeating
from XMLMain as M

结果：

field1      field2      field3 FormName                Repeating
----------- ----------- ------ ----------------------- -----------------------
1           2           1      2014-04-01 15:11:47.000 hi,hello,how are  you?

文章来源: How to read XML column in SQL Server 2008?