SQL Server (XML Data) Through ODBC to MS Access
SVCitian
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.
Stefan Hoffmann
On 09.12.2009 14:46, SVCitian wrote:
> 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***
Yes, you may use a view on the SQL Server parsing the XML (reading the
data):
http://technet.microsoft.com/en-us/library/ms190798.aspx
For editing you may use an INSTEAD OF UPDATE trigger on this view or a
stored procedure.
mfG
--> stefan <--