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>catman@gmail.com</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>catman@gmail.com</Email></Title></Group1>
10001   <Group1><Title><Name>Mary Sue</Name><Phone>381-2342</Phone><Email>thebestdude@gmail.com</Email></Title></Group1>
10002   <Group1><Title><Name>Mark Dark</Name><Phone>312-7626</Phone><Email>mybook231@gmail.com</Email></Title></Group1>
10003   <Group1><Title><Name>Garth Dan</Name><Phone>341-4572</Phone><Email>lampshade032@gmail.com</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    catman@gmail.com                NULL
10001   Mary Sue    381-2342    thebestdude@gmail.com           NULL
10002   Mark Dark   312-7626    mybook231@gmail.com             NULL
10003   Garth Dan   341-4572    lampshade032@gmail.com         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 =)

This is a great question. It's unfortunate someone decided to dump information in this format into one column of a sql table. But it happened to me as well! — Pramod Mangalore, Feb 21 '16 at 02:40

score 14 · Accepted Answer · edited May 23 '17 at 11:48

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

Thanks. I think this will work well. However, is it possible to make it dynamic, as in, not having to write out (/Group1/Title/Name) or AS Name, AS Phone, AS Email, etc. Like, is there some way to say "output the children of this parent and make columns for each one"? Or is that something which is not possible in SQL Query statements. (I have my web developer hat on right now). — Micro, May 15 '14 at 15:37
It may be possible, but I've never done it or seen it done. You might try posting another SO question about this. — tgolisch, May 15 '14 at 16:45

score -1 · Answer 2 · edited Jan 22 '20 at 18:45

--OBTAIN THE XML DATA
     declare @xml as xml = '
     <division>
      <linea>
        <id_division>01</id_division>
        <division>REPUESTOS</division>
      </linea>
      <linea>
        <id_division>02</id_division>
        <division>BATERIAS</division>
      </linea>
      <linea>
        <id_division>03</id_division>
        <division>LUBRICANTES</division>
      </linea>
      <linea>
        <id_division>04</id_division>
        <division>NEUMATICOS</division>
      </linea>
    </division>'

-- create table or you can use existing table for structure
            create table #tmp_table (
                                    id_division integer,
                                    division nvarchar(20)
                                    )

            declare @docHandle int
            EXEC sp_xml_preparedocument @docHandle OUTPUT, @xml;  

            SELECT *
                FROM OPENXML(@docHandle, '/division/linea',3)   
                WITH #tmp_table;  

            ---DELETE tmp_TABLE
            drop table #tmp_table

Convert XML column to Table Data in MS SQL Query.

2 Answers2

Linked