2005 Query

I'm having trouble with a SQL Server 2005 XML query.
<Locations>
<Location>
<City>Denver</City>
<State>CO</State>
<ZipCode>80209</ZipCode>
</Location>
<Location>
<City>Oklahoma City</City>
<State>OK</State>
<ZipCode>74804</ZipCode>
</Location>
</Locations>
The query below retuns 1 record: 8020974804 where I want it to return two
records:
80209
74804
How do I configure the query below to do that?
select Convert(nvarchar (100),Locations.query('
data(/Job/Locations/Location/ZipCode)
')) as zipcode
from Locations
You may want nodes() method.
select a.b.query('.')
from
Locations
cross apply
locations.nodes('
/Job/Locations/Location/ZipCode
') a(b)
Pohwan Han. Seoul. Have a nice day.
"Nick K" <nospam@.hotmail.com> wrote in message
news:eN1M%234aKGHA.3200@.tk2msftngp13.phx.gbl...
> I'm having trouble with a SQL Server 2005 XML query.
> <Locations>
> <Location>
> <City>Denver</City>
> <State>CO</State>
> <ZipCode>80209</ZipCode>
> </Location>
> <Location>
> <City>Oklahoma City</City>
> <State>OK</State>
> <ZipCode>74804</ZipCode>
> </Location>
> </Locations>
> The query below retuns 1 record: 8020974804 where I want it to return two
> records:
> 80209
> 74804
> How do I configure the query below to do that?
> select Convert(nvarchar (100),Locations.query('
> data(/Job/Locations/Location/ZipCode)
> ')) as zipcode
> from Locations
>
|||You probably also want to use
select a.b.value('.', 'int')
instead of the query() method call since you want SQL scalar values and not
XML text nodes.
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%23C8LRztKGHA.344@.TK2MSFTNGP11.phx.gbl...
> You may want nodes() method.
> select a.b.query('.')
> from
> Locations
> cross apply
> locations.nodes('
> /Job/Locations/Location/ZipCode
> ') a(b)
> --
> Pohwan Han. Seoul. Have a nice day.
> "Nick K" <nospam@.hotmail.com> wrote in message
> news:eN1M%234aKGHA.3200@.tk2msftngp13.phx.gbl...
>