Convert XML to rows

Question

Let's say i've got xml stored in a database. It contains a list of contacts, like so:

<Person>
 <Name>Irwin</Name>
 <Address>Home, In a place</Address>
 <Contact type="mobile">7771234</Contact>
 <Contact type="home">6311234</Contact>
 <Contact type="work">6352234</Contact>
 <Contact type="fax">6352238</Contact>
</Person>

It's stored in an xml column in a sql server database, in a table with this structure:

TABLE [Contacts](
 [ID] [bigint] IDENTITY(1,1) NOT NULL,
 [Info] [xml] NOT NULL,
 [Status] [tinyint] NOT NULL,
 [CreateTime] [datetime] NOT NULL,
) ON [PRIMARY]

I would like to write a query which converts the Contact elements into rows of a new table, matched with the ID field of the Contacts table.

I've tried this:

SELECT  Cast(Request.query('/Person/Contact/text()')  as varchar(100)) as [Number], ID 
FROM Contacts

But it pulls all the data from a given xml fragment in the column and puts it all in one row together with the ID of that row, like this:

Number, ID

7771234631123463522346352238, 1500

When what i would like to get is this:

Number, ID

7771234, 1500

6311234, 1500

6352234, 1500

6352238, 1500

Can you point me in the right direction?

Answer 1

A pointer only...

You'll need an explicit xpath query for each column you want in the result. The query you have is pulling all the text from the XML into a single column.

Answer 2

if the numbers are all the same length (7), try to use a substring

select 
substring((Cast(Request.query('/Person/Contact/text()')  as varchar(100))),0,8) mobile,
substring((Cast(Request.query('/Person/Contact/text()')  as varchar(100))),8,16) home,
substring((Cast(Request.query('/Person/Contact/text()')  as varchar(100))),16,24) work,
substring((Cast(Request.query('/Person/Contact/text()')  as varchar(100))),24,32) fax,
id
from contacts

Answer 3

Use the CROSS APPLY and xml methods

DECLARE @t TABLE ( ID INT, tag XML )

INSERT  INTO @t
        ( ID ,
          tag 
        )
VALUES  ( 1500 , -- ID - int
          '<Person> 
      <Name>Irwin</Name> 
      <Address>Home, In a place</Address> 
      <Contact type="mobile">7771234</Contact> 
      <Contact type="home">6311234</Contact> 
      <Contact type="work">6352234</Contact> 
      <Contact type="fax">6352238</Contact>
       </Person>'          
        ) 

SELECT  Number = Contact.value('.', 'varchar(MAX)') ,
        t.id
FROM    @t t
        CROSS APPLY tag.nodes('/Person/Contact') AS tag ( Contact )