Convert XML column to Table Data in MS SQL Query.

Question

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:

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

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

SELECT TheKey, TheData FROM MyTable;

Output in SQL Server Management Studio:

TheKey  TheData
10000   <Group1><Title><Name>John Doe</Name><Phone>893-3421</Phone><Email>[email protected]</Email></Title></Group1>
10001   <Group1><Title><Name>Mary Sue</Name><Phone>381-2342</Phone><Email>[email protected]</Email></Title></Group1>
10002   <Group1><Title><Name>Mark Dark</Name><Phone>312-7626</Phone><Email>[email protected]</Email></Title></Group1>
10003   <Group1><Title><Name>Garth Dan</Name><Phone>341-4572</Phone><Email>[email protected]</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:

TheKey  Name        Phone       Email                          State
10000   John Doe    893-3421    [email protected]                NULL
10001   Mary Sue    381-2342    [email protected]           NULL
10002   Mark Dark   312-7626    [email protected]             NULL
10003   Garth Dan   341-4572    [email protected]         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?

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

Answer 1

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

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:

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