xml query help returning multiple nodes

I've been working with SQL for a long time but new systems require me to learn XQuery.

I have had a good search but can't really find a newbie answer to this one, I'm 开发者_运维技巧trying to query an XML field to return multiple rows of data where a client has multiple telephone numbers. The XML field is stored at the client level and is of the form:

   <p1:Telephone>
     <p1:Type code="Home">Home</p1:Type>
     <p1:TelephoneNumber>01234 987654</p1:TelephoneNumber>
   </p1:Telephone>
   <p1:Telephone>
     <p1:Type code="Business">Business</p1:Type>
     <p1:TelephoneNumber>01324 123456</p1:TelephoneNumber>
   </p1:Telephone>

The client details are above this in the XML but this is the bit I'm struggling with.

What I would like is to return the data to query analyser as distinct columns, i.e.

    Client A | Home | 01234 987654
    Client A | Business | 01324 1234546
    Client B | .... | ....

Can someone point me in the right direction for this?

Many Thanks,

Jon

You can use the new XML data type like this assuming the XML looks like in the answer provided by openshac and YourTable has one ClientName column and one XMLCol column.

;with xmlnamespaces ('http://org.test' as p1)
select X.ClientName,
       T.N.value('p1:Type[1]/@code', 'varchar(25)') as TypeCode,
       T.N.value('p1:Type[1]', 'varchar(25)') as TypeValue,
       T.N.value('p1:TelephoneNumber[1]', 'varchar(25)') as TelephoneNumber
from YourTable as X
  cross apply X.XMLCol.nodes('/root/p1:Telephone') as T(N)

Edit

;with xmlnamespaces ('f2.co.uk/f2/1.0' as p1) 
select X.ClientReference, 
       T.N.value('p1:Type[1]/@code', 'varchar(25)') as TypeCode, 
       T.N.value('p1:Type[1]', 'varchar(25)') as TypeValue, 
       T.N.value('p1:TelephoneNumber[1]', 'varchar(25)') as TelephoneNumber 
from #tempa as X 
  cross apply X.[Xml].nodes('//p1:CommunicationChannel/p1:Telephone') AS T(N)

You haven't included your full XML so I can't see where you get the client info from, however try this:

    DECLARE @xml nvarchar(max) = '<root xmlns:p1="http://org.test">
   <p1:Telephone>
     <p1:Type code="Home">Home</p1:Type>
     <p1:TelephoneNumber>01234 987654</p1:TelephoneNumber>
   </p1:Telephone>
   <p1:Telephone>
     <p1:Type code="Business">Business</p1:Type>
     <p1:TelephoneNumber>01324 123456</p1:TelephoneNumber>
   </p1:Telephone></root>'

    DECLARE @xml_handle int  
    EXEC sp_XML_preparedocument @xml_handle OUTPUT, @xml, '<root xmlns:p1="http://org.test" />'

    SELECT * FROM 
    OPENXML( @xml_handle, '//p1:Telephone') 
    WITH (
        [Type]              varchar(10) './p1:Type',
        [Code]              varchar(10) './p1:Type/@code',
        [TelephoneNumber]   varchar(10) './p1:TelephoneNumber'
    )

  EXEC sp_xml_removedocument @xml_handle    

returns:

Type,Code,TelephoneNumber

Home,Home,01234 9876

Business,Business,01324 1234