Return all Elements from XML Segment (SQL 2005)

Hi,
I need to be able to return all Elements from an XML segment that is
stored in a SQL 2005 XML column. The column does not have an associated
schema as the XML segment has multiple formats. All that is consistent is
that there will be a common root element. After that any number of Elements
may exist within it.

For example:
<objdata>
<ProjectName>My Project Name Here</ProjectName>
<Project_Number>1150700</Project_Number>
<Latitude_Decimal>61.17</Latitude_Decimal>
<Longitude_Decimal>150.02</Longitude_Decimal>
</objdata>

would need to return:

ProjectName
Project_Number
Latitude_Decimal
Longitude_Decimal

Any suggestions will be considered. I'd prefer to refrain from using
Cursors or CLR.

Thanks,
- Kurt

Try:

DECLARE @xml XML

SET @xml = '
<objdata>
<ProjectName>My Project Name Here</ProjectName>
<Project_Number>1150700</Project_Number>
<Latitude_Decimal>61.17</Latitude_Decimal>
<Longitude_Decimal>150.02</Longitude_Decimal>
</objdata>
'

SELECT x.y.value( 'local-name(.)', 'varchar(100)' )
FROM @xml.nodes( 'objdata/*' ) x(y)

HTH
wBob


"kkam99504" wrote:

> Hi,
> I need to be able to return all Elements from an XML segment that is
> stored in a SQL 2005 XML column. The column does not have an associated
> schema as the XML segment has multiple formats. All that is consistent is
> that there will be a common root element. After that any number of Elements
> may exist within it.
>
> For example:
> <objdata>
> <ProjectName>My Project Name Here</ProjectName>
> <Project_Number>1150700</Project_Number>
> <Latitude_Decimal>61.17</Latitude_Decimal>
> <Longitude_Decimal>150.02</Longitude_Decimal>
> </objdata>
>
> would need to return:
>
> ProjectName
> Project_Number
> Latitude_Decimal
> Longitude_Decimal
>
> Any suggestions will be considered. I'd prefer to refrain from using
> Cursors or CLR.
>
> Thanks,
> - Kurt