xml to ado recordset

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

li - 17 Feb 2005 23:11 GMT

I have a very simple xml file test.xml, which looks as following:
<?xml version="1.0" encoding="UTF-8"?>

<PDCdrom >
<CheckItem></CheckItem>
<CheckItem></CheckItem>
<CheckItem></CheckItem>
</PDCdrom>

I need to read this file into a disconnected recordset. I know msxml can do
something very similar but the xml has to be in certain format which I can
not do. Does anyone know how to read this file as it is into a recordset?

Reply to this Message

Ant - 20 Feb 2005 12:27 GMT

Hi Li,
Is this what you want to do? I don't think you can include double quotations
in a string built in VB so you'll need to use this in a stored procedure &
call the SP using your recordsets source property

DECLARE @idHndl int
DECLARE @doc varchar(1000)

SET @doc =
'
<PDCdrom >
<CheckItem><RowOne FirstCol = "AAA" SecondCol = "111"/></CheckItem>
<CheckItem><RowOne FirstCol = "BBB" SecondCol = "222"/></CheckItem>
<CheckItem><RowOne FirstCol = "CCC" SecondCol = "333"/></CheckItem>
</PDCdrom>
'

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idHndl OUTPUT, @doc

-- Execute a SELECT statement with OPENXML.
SELECT *
FROM OPENXML (@idHndl, '/PDCdrom/CheckItem/RowOne',1)
WITH (Col1 varchar(20) '@FirstCol',
Col2 varchar(20) '@SecondCol')

Hope that's what you're after
Ant

> I have a very simple xml file test.xml, which looks as following:
> <?xml version="1.0" encoding="UTF-8"?>
[quoted text clipped - 8 lines]
> something very similar but the xml has to be in certain format which I can
> not do. Does anyone know how to read this file as it is into a recordset?

Reply to this Message

li - 25 Feb 2005 23:49 GMT

Ant,

Thanks for your reply. I'm actually using SQL Sever 7. Embarrassing, isn't
it? I did find a way to build my recordset thougth. This is what I did:

Dim rsXML As ADODB.Recordset
Dim objDoc As DOMDocument
Dim objNodes As IXMLDOMNodeList
Dim objCheckItem As IXMLDOMNode

Set rsXML = New ADODB.Recordset
rsXML.fields.Append "Field1", adVarChar, 25
rsXML.fields.Append "Field2", adDate
rsXML.open
Set objDoc = New DOMDocument

Set objNodes = objDoc.selectNodes("/PDCdrom/CheckItem")
For Each objCheckItem In objNodes
rsXML.AddNew
rsXML.fields("field1") = objCheckItem.Attributes(0).nodeValue
rsXML.fields("field2") = objCheckItem.Attributes(1).nodeValue
rsXML.update
Next

It's really primitive but sufficient because my XML document is really
simple. This is all I want to do. Do you have any better way to do it?

Li

> Hi Li,
> Is this what you want to do? I don't think you can include double quotations
[quoted text clipped - 37 lines]
> > something very similar but the xml has to be in certain format which I can
> > not do. Does anyone know how to read this file as it is into a recordset?

Reply to this Message

VB Forum / Database Access / February 2005

xml to ado recordset