在MS SQL查询中将XML列转换为表数据。

In a table in my data base I have a column in which the entire entry is a long XML String with the following structure:

在我的数据库中的一个表中，我有一个列，其中整个条目是一个长XML字符串，其结构如下:

  <Group1>
      <Title>
          <Name>John Doe</Name>
          <Phone>555-3421</Phone>
          <Email>[e-mail]</Email>
          (+more)
      </Title>
  </Group1>

This is my SELECT Statement and here is what it outputs:

这是我的SELECT语句这里是它的输出:

SELECT TheKey, TheData FROM MyTable;

Output in SQL Server Management Studio:

SQL Server Management Studio输出:

TheKey  TheData
10000   <Group1><Title><Name>John Doe</Name><Phone>893-3421</Phone><Email>[e-mail]</Email></Title></Group1>
10001   <Group1><Title><Name>Mary Sue</Name><Phone>381-2342</Phone><Email>[e-mail]</Email></Title></Group1>
10002   <Group1><Title><Name>Mark Dark</Name><Phone>312-7626</Phone><Email>[e-mail]</Email></Title></Group1>
10003   <Group1><Title><Name>Garth Dan</Name><Phone>341-4572</Phone><Email>[e-mail]</Email><State>California</State></Title></Group1>

I would like to write some sort of MS SQL query that will return the data like this to me:

我想写一些MS SQL查询，它会将这样的数据返回给我:

TheKey  Name        Phone       Email                          State
10000   John Doe    893-3421    [e-mail]                NULL
10001   Mary Sue    381-2342    [e-mail]           NULL
10002   Mark Dark   312-7626    [e-mail]             NULL
10003   Garth Dan   341-4572    [e-mail]         California

Notice how the last entry had an extra XML tag <State> which the other entries did not. I would like it to be flexible like this - using some sort of parent/child references?

注意，最后一个条目有一个额外的XML标记 ，而其他条目没有。我希望它像这样灵活——使用某种类型的父/子引用?

Any help doing this would be greatly appreciated, I just can't seem to find anything like it anywhere =)

如果有任何帮助，我将不胜感激，我在任何地方都找不到这样的东西=)

1 个解决方案

#1

If your XML column is stored as type XML, then you can use an XPath query to get each column. Like this:

如果XML列存储为XML类型，那么可以使用XPath查询获取每个列。是这样的:

SELECT TheKey, 
    TheData.value('(/Group1/Title/Name)[1]', 'varchar(100)') AS Name,
    TheData.value('(/Group1/Title/Phone)[1]', 'varchar(100)') AS Phone,
    TheData.value('(/Group1/Title/Email)[1]', 'varchar(250)') AS Email,
    TheData.value('(/Group1/Title/State)[1]', 'varchar(100)') AS [State]
FROM MyTable

If the column MyData is varchar instead of XML, then you can cast it during the query. Like this:

如果列myis varchar而不是XML，那么可以在查询期间对其进行强制转换。是这样的:

SELECT TheKey, 
    Cast(TheData AS XML).value('(/Group1/Title/Name)[1]', 'varchar(100)') AS Name,
    Cast(TheData AS XML).value('(/Group1/Title/Phone)[1]', 'varchar(100)') AS Phone,
    Cast(TheData AS XML).value('(/Group1/Title/Email)[1]', 'varchar(250)') AS Email,
    Cast(TheData AS XML).value('(/Group1/Title/State)[1]', 'varchar(100)') AS [State]
FROM MyTable

Btw, I got this information from this other SO article. (vote it up to make it easier for others to find it) How to query xml column in tsql

顺便说一句，我从另一篇SO文章中得到了这个信息。如何在tsql中查询xml列