retrieving XML data from table

Hi,
I have a table with two field one of them is an identity field as record ID
and another is a ntext field named: xData
XData field contain an xml data like below:
<root>
<ta Name="bill" Job="eng"/>
<ta Name="john" Job="Mng"/>
<ta Name="Tom" Job="Sup"/>
</root>
And in the next record:
………
I can retrieve xml data from xData field for each record with openxml () to
a temptable .
Also I can update this table with SQL Statement like Delete,Update and insert.
Now after updating temptable, I want update xData filed with updated data
(as a xml format ).
Unfortunately I can not return data with XML format from temptable.
Please tell me how can I do that.
I assume you are using SQL Server 2000 (it would be much easier in SQL
Server 2005 where you can use the XML datatype and XQuery/XML-DML).
I assume that you would like to use FOR XML to reconstruct the XML and
reinsert it into the ntext field. You can do so, but you would need to write
client-code that retrieves the XML from the server and then gives it back
into the server. That code can then be called from within the SQL Server
using the sp_OA stored procs.
Also note that you will run into issues with your OpenXML approach below if
the data will be larger than 8kBytes, unless you are using some other
workaround of the limitation of SQL Server 2000 that you cannot have
variables of type ntext (note that SQL Server 2005 is better again).
Best regards
Michael
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:6E1080E4-3A0A-4E53-B8A6-616AF393FFFB@.microsoft.com...
> Hi,
> I have a table with two field one of them is an identity field as record
> ID
> and another is a ntext field named: xData
> XData field contain an xml data like below:
> <root>
> <ta Name="bill" Job="eng"/>
> <ta Name="john" Job="Mng"/>
> <ta Name="Tom" Job="Sup"/>
> </root>
> And in the next record:
> ...
> I can retrieve xml data from xData field for each record with openxml ()
> to
> a temptable .
> Also I can update this table with SQL Statement like Delete,Update and
> insert.
> Now after updating temptable, I want update xData filed with updated data
> (as a xml format ).
> Unfortunately I can not return data with XML format from temptable.
> Please tell me how can I do that.
>
sql

retrieving XML data from table

Hi,
I have a table with two field one of them is an identity field as record ID
and another is a ntext field named: xData
XData field contain an xml data like below:
<root>
<ta Name="bill" Job="eng"/>
<ta Name="john" Job="Mng"/>
<ta Name="Tom" Job="Sup"/>
</root>
And in the next record:
………
I can retrieve xml data from xData field for each record with openxml () to
a temptable .
Also I can update this table with SQL Statement like Delete,Update and inser
t.
Now after updating temptable, I want update xData filed with updated data
(as a xml format ).
Unfortunately I can not return data with XML format from temptable.
Please tell me how can I do that.I assume you are using SQL Server 2000 (it would be much easier in SQL
Server 2005 where you can use the XML datatype and XQuery/XML-DML).
I assume that you would like to use FOR XML to reconstruct the XML and
reinsert it into the ntext field. You can do so, but you would need to write
client-code that retrieves the XML from the server and then gives it back
into the server. That code can then be called from within the SQL Server
using the sp_OA stored procs.
Also note that you will run into issues with your OpenXML approach below if
the data will be larger than 8kBytes, unless you are using some other
workaround of the limitation of SQL Server 2000 that you cannot have
variables of type ntext (note that SQL Server 2005 is better again).
Best regards
Michael
"Mehdi" <Mehdi@.discussions.microsoft.com> wrote in message
news:6E1080E4-3A0A-4E53-B8A6-616AF393FFFB@.microsoft.com...
> Hi,
> I have a table with two field one of them is an identity field as record
> ID
> and another is a ntext field named: xData
> XData field contain an xml data like below:
> <root>
> <ta Name="bill" Job="eng"/>
> <ta Name="john" Job="Mng"/>
> <ta Name="Tom" Job="Sup"/>
> </root>
> And in the next record:
> ...
> I can retrieve xml data from xData field for each record with openxml ()
> to
> a temptable .
> Also I can update this table with SQL Statement like Delete,Update and
> insert.
> Now after updating temptable, I want update xData filed with updated data
> (as a xml format ).
> Unfortunately I can not return data with XML format from temptable.
> Please tell me how can I do that.
>

retrieving parent(ascendants)

i have a dimension named 'sdoc' which has 2 members
1) symptom word( first hierarchical level) and
2) doc id (second hierarchical level)

more than one 'symptom word' can have the same 'doc id'.
i want to know which all symptom words have a particular doc id (say 10.)
if i give [10].parent then it returns only the first parent whereas it actually has more than one parent.
how to retrieve all the parents.more than one 'symptom word' can have the same 'doc id'. So...there is a one-to-many relationship between 'doc id' and 'symptom word'?
if i give [10].parent then it returns only the first parent whereas it actually has more than one parent.So...there is a one-to-many relationship between 'symptom word' and 'doc id'?

Huh?

In database relationships, records normally have only one parent...

Please post the DDL code for the tables you are referencing, along with the SQL code you have tried.