MS SQL XML - query xml datatype

Author

query xml datatype

--Marty

2005-09-14, 1:23 pm

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?

Eugene Kogan [MSFT]

2005-09-14, 8:24 pm

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())& #91;1]','varchar(200
)') AS
FirstName,
Info.value('(/m:info/m:lastname/text())& #91;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.googlegroups.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?
>

Sponsored Links