query xml datatype

In SQL 2005, we've defined a column as type "xml". We'd like to query
that column so that its nodes are returned in traditional columnar
format. For instance, to get the first & last name nodes, we have code
like...
Select cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:firstname)') as varchar(200)) as FirstName,
cast(Info.query('
declare namespace m="http://tempuri.org/MyInfo";
data(/m:info/m:lastname)') as varchar(200)) as LastName
from MyTest
That seems like a lot of work to get the first & last name in columnar
format. Is there a better way to do this?
You can't write it a whole lot simpler.
Here's what I would write if firstname and lastname elements have open
content:
WITH XMLNAMESPACES('http://tempuri.org/MyInfo' AS m)
SELECT
Info.value('(/m:info/m:firstname/text())[1]','varchar(200)') AS
FirstName,
Info.value('(/m:info/m:lastname/text())[1]','varchar(200)') AS LastName
FROM MyTest
Note that value() method does both atomization (data()) of the resulting
XQuery sequence element (must be singleton) and mapping it to a SQL type
provided as the 2nd parameter.
If your XML column is typed and firstname and lastname elements have simple
type/content than you'd need to remove "/text()" from the above XQuery
expressions.
Note that if there could be multiple firstname/lastname elements per XML
instance and you needed each of them on a separate row you'd use nodes()
method in FROM clause.
Regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"--Marty" <Martin.McDonald@.us.logicalis.com> wrote in message
news:1126718758.082803.55550@.g47g2000cwa.googlegro ups.com...
> In SQL 2005, we've defined a column as type "xml". We'd like to query
> that column so that its nodes are returned in traditional columnar
> format. For instance, to get the first & last name nodes, we have code
> like...
> Select cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:firstname)') as varchar(200)) as FirstName,
> cast(Info.query('
> declare namespace m="http://tempuri.org/MyInfo";
> data(/m:info/m:lastname)') as varchar(200)) as LastName
> from MyTest
> That seems like a lot of work to get the first & last name in columnar
> format. Is there a better way to do this?
>