CODEWITHSUNDEEP
xmlsql-server-2008xquery
user898205
user898205

Reputation: 25

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

Upvotes: 2

Views: 2588

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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)

Upvotes: 2

openshac
openshac

Reputation: 5165

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

Upvotes: 0

Related Questions