Hello,

In one database in my company.

Previously the data in SQL Server, the tables were simple text and
number formats and it was easy for me to link the data through ODBC to
MS Access and I could do all sorts of queries and calculations, etc.

But, now (after several months) I realize that the DBA and Programmer
changed, the data in SQL Server to be XML Data Type in the new setup.

In SQL Server.

dbo.FlightDocuments Table

docID, int
docInfo, xml
docLogs, xml
and so on.

and I see the data in Access (through ODBC) for one of the fields of
the above table like this:

<Cargo><Local><Mawb>1</Mawb><Hawb>0</Hawb><Weight>139</
Weight><Pcs>14</
Pcs></Local><Transit><Mawb>2</Mawb><Hawb>0</Hawb><Weight>3457</
Weight><Pcs>161</Pcs></Transit><BUP><Local><MDP><Mawb>0</
Mawb><Hawb>0</
Hawb><Weight>0</Weight><Pcs>0</Pcs><Unit>0</Unit></MDP><LDP><Mawb>0</
Mawb><Hawb>0</Hawb><Weight>0</Weight><Pcs>0</Pcs><Unit>0</Unit></
LDP><LD3><Mawb>0</Mawb><Hawb>0</Hawb><Weight>0</Weight><Pcs>0</
Pcs><Unit>0</Unit></LD3></Local><Transit><MDP><Mawb>0</Mawb><Hawb>0</
Hawb><Weight>0</Weight><Pcs>0</Pcs><Unit>0</Unit></MDP><LDP><Mawb>0</
Mawb><Hawb>0</Hawb><Weight>0</Weight><Pcs>0</Pcs><Unit>0</Unit></
LDP><LD3><Mawb>0</Mawb><Hawb>0</Hawb><Weight>0</Weight><Pcs>0</
Pcs><Unit>0</Unit></LD3></Transit></BUP></Cargo>

In fact, every field looks like above but with different xml tags of
course.

I don't know how to manipulate it using Access Query.

Yes.. I may use perl or some other scripts to extract the data after
exporting it to a text file or something... But, I think there should
be a simpler way.

Is there a simpler way where I can use all this data like I was always
used to:

Select * from FlightDocuments Where ***something here***

ID, MawbWeight, HawbWeight, MawbPcs, HawbPcs...
931, 160, 230, 23, 120
and so on

And, all the data is magically transformed to a readable view.

Please help.

hi,

On 09.12.2009 14:46, SVCitian wrote:
data):

http://technet.microsoft.com/en-us/l.../ms190798.aspx

For editing you may use an INSTEAD OF UPDATE trigger on this view or a
stored procedure.


mfG
--> stefan <--