Solved

Get data fron XML field

Posted on 2008-10-07
19
614 Views
Last Modified: 2012-08-13
Hi,

How to read data from XML field with ADO Recordset:Fieldget and write data with Recordset:Fieldput ?

Below the SQL script to create the data sample :
CREATE TABLE test
(
  fldtest XML
);
INSERT INTO test (fldtest) VALUES ('<?xml version="1.0" encoding="windows-1252"?><E0><E01 LT="N">6.000000</E01></E0>');

Thanks in advance,

Phil
0
Comment
Question by:s2000_com
  • 7
  • 3
  • 2
  • +2
19 Comments
 
LVL 5

Expert Comment

by:almander
ID: 23008506
Hi Phil,

I assume you are using VB, since you are talking about ADO.

Also it sounds like you want to create a new recordset based upon the data in an xml field. I.E.
Use the data in test.fieldtest to create a recordset.

If so you could call the attached RecordsetFromXMLString passing in the text in test.fieldtest.

Public Function RecordsetFromXMLString(sXML As String) As Recordset
 

    Dim oStream As ADODB.Stream

    Set oStream = New ADODB.Stream

    

    oStream.Open

    oStream.WriteText sXML   'Give the XML string to the ADO Stream
 

    oStream.Position = 0    'Set the stream position to the start
 

    Dim oRecordset As ADODB.Recordset

    Set oRecordset = New ADODB.Recordset

       

    oRecordset.Open oStream    'Open a recordset from the stream
 

    oStream.Close

    Set oStream = Nothing
 

    Set RecordsetFromXMLString = oRecordset  'Return the recordset
 

    Set oRecordset = Nothing
 

End Function

Open in new window

0
 

Author Comment

by:s2000_com
ID: 23011583
Hi almander,

I've seen this piece of code already on internet but was not able to apply to my sample: Can you show me how to do using my sample ?

TIA,

Phil
0
 
LVL 5

Expert Comment

by:almander
ID: 23015216
Can you attach your sample vb app.
0
 

Author Comment

by:s2000_com
ID: 23148977
almander,

I don't have any vb sample. What I mean by "my sample" is the SQL script above.

Can you show how to code using my SQL script sample above ?
0
 
LVL 84
ID: 23363075
Your question isn't clear; your sample, of course, creates a Table and inserts a single value in that Table ... from that point, what are you trying to do? And what environment will you be working?
0
 

Author Comment

by:s2000_com
ID: 23364394
Hi LSMConsulting !

I'm trying to know how to read (and optionally write) the XML field with ADO Recordset like shown in the code snippet.
set conn = Server.CreateObject("ADODB.Connection")

set rs = Server.CreateObject("ADODB.Recordset")
 

rs.open "Select * from test", conn
 

response.write(rs.Fields(0).Value)

Open in new window

0
 
LVL 84
ID: 23365440
But you're pulling this from a Table, not an XML file ... I realize that your Fields(0) would contain a portion of an XML file, but is that what you need to get? The value from that portion?
0
 

Author Comment

by:s2000_com
ID: 23370789
Yes the field is XML type containing XML data and I need to read and write it with ADO.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Expert Comment

by:mdougan
ID: 23739119
Well, the big question for me was whether ADO could support the new SQL Server xml data type.  Just browsed Microsoft and they say ADO 2.0 does (so, might depend on what version of Visual Studio/VB you are working with).

Anyway, this link has some great examples:

http://msdn.microsoft.com/en-us/library/ms971534.aspx

0
 
LVL 18

Expert Comment

by:mdougan
ID: 23739174
looks like their code samples are in C# but it is pretty easy to convert to VB from C# syntax... let me know if you need help with that.
0
 
LVL 39

Expert Comment

by:abel
ID: 23739571
I must say that it is a bit vague what this is about, but since this question has been open so long and several experts have given it a try to no avail, let me first try get the question clear.

Situation: a table with a (single) data field (text/varchar/xml?) is filled with XML data (assumption: it is XML compliant, assumption: XML type data field).

Request:

  1. getting this data out there using a language, VB, using a technique, ADO.
  2. getting data into that field or in a new record
  3. changing data in that field
Why the request originally states FieldGet/Put I don't know, personally, it seems that any valid approach from VB.NET would do. No wait!. The 2nd sample looks more like VBScript, which would explain the ADO approach. As has already been stated by MDougan, you'll need at least ADO 2.0 for it to work.

Can the OP verify this rephrasing of the question? And is ADO 2.0 an option?
0
 

Author Comment

by:s2000_com
ID: 23740134
mdougan: I'm still using ADO 2.8 and NOT ADO.NET. Have you some samples using ADO 2.8 ?

abel: as written in my first post, it's an XML data field and as replied to mdougan, I'm still using ADO 2.8 and not ADO.NET

Can you both confirm that it's NOT possible to do this operation with ADO 2.8 (not ADO.NET) ?
0
 
LVL 18

Accepted Solution

by:
mdougan earned 500 total points
ID: 23740499
s2000 com
I'll research on the ADO 2.8 vs ADO.NET, but my first impression would be that only ADO.NET would support it.  The XML data type is new to MS SQL Server 2005, and it is hard enough to get Microsoft to support that in .NET much less any other environments.

So, what are you coding in?  VB6?  Don't get me wrong, I love VB6, but, you can't expect Microsoft to do timely support for their older environments.

Abel and the other experts... I don't think s2000 com's question was unclear at all.  SQL Server 2005 now has a data type called XML which has different access methods than text fields.  Most of us are used to stuffing XML into text fields, and so, this question throws us off a bit... but, if you read the question carefully, you can understand what is being asked.
0
 

Author Comment

by:s2000_com
ID: 23740779
Thanks mdougan to support me in my quest !

We are using Visual-Objects ( http://www.cavo.com) which is a Win32 (not .NET) language and are in a migration project from xBase to SQL Server thru ADO 2.8. In xBase we have stored XML data in memo (text) fields for 4 years now. During the migration to SQL Server (some months ago) we tried to store XML data in XML fields (logic !). Finally, at that time, we decided to store XML in varchar(max) but I've continued my research upto now. From September 2009, we will migrate our code to Vulcan.NET (www.govulcan.net). So it seems that we will migrate the XML data from VARCHAR(MAX) field to XML field at that time.

If you have more infos about ur research, thanks to let me know. Anyway as you were the only one who uinderstand my request : points are for you.
0
 
LVL 39

Expert Comment

by:abel
ID: 23742427
> I don't think s2000 com's question was unclear at all.

Maybe not, but if people re-read the whole thread after the question had been silent for a month, and if you see from some of the posts that there is some unclarity (and I don't know how often the question was updated) I tried to wrap it up.

But on second thought (and in the morning, not late at night) you are right, the question itself is clear ;)

And on third thought: that final comment of the OP makes everything much more crystal clear on what actually is the task at hand and would allow us to help more to the point. Though this question is closed, you (the OP) might want to put that last comment into a new question as quite some people can give you hints on how to tackle that task :)
0
 

Author Comment

by:s2000_com
ID: 23742505
abel: Thanks for the advice. For my next requests, I will explain more the situation, causes and consequences.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Calculate values in an array 18 32
TSQL Where clause for Date with CASE - what is wrong? 11 71
SQL profiler equivalent in MS-Access 3 40
Permissions on Database 11 36
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now