Return a record set of element names

Hi,

Does anyone know how to return a list of element names from an xml document?

eg.

Code Snippet

<values>
<name>Brian</name>
<lastName>Smith</lastName>
<tel>999-123456</tel>
</values>

the result set I'm after is a table with two columns (lets say col_name and col_value)

col_name col_value
name Brian
lastName Smith
tel 999-123456

my biggest problem is extracting the element name - any ideas

Many Thanks,

Jan.

Here is an example doing that using the XQuery nodes method to shred the XML into nodes and then the local-name XQuery function:

Code Snippet

DECLARE @.x xml;

SET @.x ='<values>

<name>Brian</name>

<lastName>Smith</lastName>

<tel>999-123456</tel>

</values>';

SELECT

T.xcol.value('local-name(.)','nvarchar(20)')AScol_name

,T.xcol.value('.','nvarchar(20)')AS col_value

FROM @.x.nodes('*/*')AS T(xcol);

|||Cheers Martin,

Exactly what I was after !

Retrieve DATA from relation db from within a Cube (calculated member)

Hello,

I need the ability to retrieve data element that is stored in a relational DB from within a cube (calculated measure). Is there a way to do this? If so, can someone please indicate how? If not, is there a way to store some data which is really independant of any measure or dimension in a cube?

I appreciate your help in advance.

Thanks,

Not sure what your question is. The cube you build contans data Analysis Server reads from relational database. By accessing cube you access copy of the data retreived from relational database.

If you would like Analysis Server not to store copy of relational data, you can define storage mode for AS objects as ROLAP. In this case MDX queries will generate SQL queries ( if data is not cached ).This mode obviously gets you poor query performance compared to MOLAP storage mode, which is default mode.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Retrieve DATA from relation db from within a Cube (calculated member)

Hello,

I need the ability to retrieve data element that is stored in a relational DB from within a cube (calculated measure). Is there a way to do this? If so, can someone please indicate how? If not, is there a way to store some data which is really independant of any measure or dimension in a cube?

I appreciate your help in advance.

Thanks,

Not sure what your question is. The cube you build contans data Analysis Server reads from relational database. By accessing cube you access copy of the data retreived from relational database.

If you would like Analysis Server not to store copy of relational data, you can define storage mode for AS objects as ROLAP. In this case MDX queries will generate SQL queries ( if data is not cached ).This mode obviously gets you poor query performance compared to MOLAP storage mode, which is default mode.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.