Post reply

Query XML

  • May 10, 2012 at 2:48 pm

    #268642

    I'm wanting to query an XML site, which is really just an RSS feed. I want to be able to put the data into my database. The site that I'm looking at is:

    http://ww2.tdot.state.tn.us/tsw/GeoRSS/TDOTConstructionGeorss.xml

    Is there anyway to query that site and insert it into a table in my database?

    Thanks,

    Jordon

  • May 10, 2012 at 2:52 pm

    #1485990

    I tried this:

    SELECT *

    INTO #tmpxml

    FROM OPENROWSET(

    BULK 'http://ww2.tdot.state.tn.us/tsw/GeoRSS/TDOTConstructionGeorss.xml',

    SINGLE_BLOB) AS x

    But that didn't seem to work.

  • May 10, 2012 at 4:16 pm

    #1486031

    You're not going to be able to query a URL directly from within a query. At very least you'd need to save the current RSS feed locally and THEN import it bulk.

    Assuming you did that, you could use something like this to get at the contents:

    with

    XMLNAMESPACES ('http://www.tdot.state.tn.us/tdotsmartway/' as tsw,

    'http://www.georss.org/georss' as georss,

    'http://www.opengis.net/gml' as gml)

    select c.value('(./title/text())[1]', 'varchar(500)')

    from (Select cast(bulkColumn AS XML) g from #tmpxml) h cross apply g.nodes('/rss/channel/item') v(c)

    Otherwise you're likely looking at using SQLCLR to do this, or some external code to read from the site, and then import the XML (could use SSIS as well).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • May 10, 2012 at 4:21 pm

    #1486033

    OPENROWSET does not have HTTP resolution capabilities. OPENROWSET requires the file be available at some disk location, either local drive or UNC path, so you'll need to download the XML and store in a file to use it.

    An alternative is to implement a SQLCLR object. C# allows us to connect to a remote site over HTTP to download XML. C# cal also be used to add the XML directly to the database without storing it as a file on disk first.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply