Hi
I have an xml file, i wish on a regular basis to collect the data from the file to insert into the sql server database:
 my idea:
1. create a ado recordset and insert all necessary data from the xml file to the recordset
2. insert the recordset to to tha appropriate table within the sql server.

is this idea possible? if so how do i do it? I am using vbscript in asp.
I know that sql returns ado recordsets, can it take ado recordset?
Would appreciate any answers that help towrds solving my problem.
Regards


ak

__________________
ak

If you're using sql server 2000 take a look at the new OPENXML syntax in BOL.

thanks
im using sql server 2k
currently looking into OPENXML.
concern: my table fields and XML entity attribute names may differ

heres example of what im doing incase it helps in understanding problem better:
database table: tbDailyBannerDisplayClick
  intDailyBannerClickID
  DtDateClicked
  intBannerGroupID
  intBannerID
  HTTP_REFERER
  intBannerClickCount
  intBannerDisplayCount

File Name: bannerClicks.xml
Entity: BannerClicks
Entity: date
  Attributes: DateClicked
Entity: bannerclick
  Attributes: intDailyBannerClickID
  Attributes: intBannerGroupID
  Attributes: intBannerID
  Attributes: HTTP_REFERER
  Attributes: DateClicked
  Attributes: intBannerClickCount
  Attributes: intBannerDisplayCount



ak

Thanks pgtips, finding it useful,
...a further question whilst still using my initaive to solve:
what if the xml resides in a seperate server from the sql server? How do i give the source file location for the OPENXML method?


DECLARE @idoc int
DECLARE @doc varchar(5000)
SET @doc ='
<bannerClicks>
    <date DateClicked="17-Feb-2004">
        <bannerclick intDailyBannerClickID="1" intBannerGroupID="2" intBannerID="3" HTTP_REFERER="bannertest.asp" DateClicked="20-Feb-2004" intBannerClickCount="1" intBannerDisplayCount=""/>
        <bannerclick intDailyBannerClickID="2" intBannerGroupID="4" intBannerID="2" HTTP_REFERER="bannertest.asp" DateClicked="20-Feb-2004" intBannerClickCount="1" intBannerDisplayCount=""/>
        <bannerclick intDailyBannerClickID="3" intBannerGroupID="5" intBannerID="5" HTTP_REFERER="bannertest.asp" DateClicked="20-Feb-2004" intBannerClickCount="1" intBannerDisplayCount=""/>
    </date>
    <date DateClicked="25-Feb-2004">
    <bannerclick intDailyBannerClickID="1" intBannerGroupID="4" intBannerID="5" HTTP_REFERER="banner.asp" DateClicked="25-Feb-2004" intBannerClickCount="2" intBannerDisplayCount="1"/>
    <bannerclick intDailyBannerClickID="2" intBannerGroupID="4" intBannerID="3" HTTP_REFERER="banner.asp" DateClicked="25-Feb-2004" intBannerClickCount="2" intBannerDisplayCount="2"/>
    <bannerclick intDailyBannerClickID="3" intBannerGroupID="4" intBannerID="4" HTTP_REFERER="banner.asp" DateClicked="25-Feb-2004" intBannerClickCount="1" intBannerDisplayCount="1"/>
    <bannerclick intDailyBannerClickID="4" intBannerGroupID="4" intBannerID="1" HTTP_REFERER="banner.asp" DateClicked="25-Feb-2004" intBannerClickCount="0" intBannerDisplayCount="1"/>
    </date>
</bannerClicks>

'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, '/bannerClicks/date[@DateClicked="25-Feb-2004"]/bannerclick',1)
            WITH (intDailyBannerClickID varchar(10),
                 intBannerGroupID varchar(20),
        intBannerID varchar(20),
        HTTP_REFERER varchar(20),
        DateClicked varchar(20),
        intBannerClickCount varchar(20),
        intBannerDisplayCount varchar(20)

)
exec sp_xml_removedocument @idoc
Select @idoc


ak

Hi ak, let me first say I haven't used this new OPENXML syntax myself, I just know that it exists and though it might be just what you needed.

From what I've read you don't supply the location of a physical file, but instead you must pass the xml as a character string.

rgds
Phil

Thanks Phil,
so now i have an xml and asp problem as the database side of the issue is resolved.
my experience with XMl is v small.
How do i convert a part of the xml into string inorder to pass on to a stored procedure using asp? anyone?

------------
SET objXMLDoc_bannerClicks = Server.CreateObject("Microsoft.XMLDOM")
objXMLDoc_bannerClicks.async = False
blnFileExist_bannerClicks = objXMLDoc_bannerClicks.Load("bannerClicks.xml")




ak

Just use the selectSingleNode() method to grab the relevant part of the XML and then use the xml property of the node object to get a string representation suitable for passing as a string parameter.

BTW don't use "Microsoft.XMLDOM" unless you really have to - its a very old version. You should at least have MSXML3 on your machine and you may even have MSXML4. Use one of these instead - progid = "MSXML2.DOMDocument.3.0" or "MSXML2.DOMDocument.4.0""

thanks that was useful.
i am now using "MSXML2.DOMDocument.3.0"

ak