Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

import XML data into SQL table via Stored Procedure

I need to write a stored procedure that will import data from a dynamic XML feed into my sql table
the xml feed would be something like
http://www.zillow.com/webservice/GetSearchResults.htm?zws-id=X1-ZWz1cwo6lcszyj_aui2x&address=12598+Dupont+Drive&citystatezip=Rancho+Cucamonga+91739
with the address being dynamic
i.e. http://www.zillow.com/webservice/GetSearchResults.htm?zws-id=X1-ZWz1cwo6lcszyj_aui2x&address=" + address + "&citystatezip=" + city + "+" + zip

I have tried
select * from OpenRowSet(BULK N'LinkGoesHere') as test
with no success.

I basically want to grab one field sestimate from the xml file and import it into a table called properties that has a field with the same name

any suggestions?
0
bob1barker
Asked:
bob1barker
  • 4
  • 3
1 Solution
 
elimesikaCommented:
HI

you have to do that dynamically.
DECLARE @xml XML, @sql nvarchar(1000), @ParameterList nvarchar(100)
			 SET @ParameterList = '@xml xml OUTPUT'

			set @sql = 'select * from OpenRowSet(BULK N''' + LinkGoesHere + ''') as test'

			--Convert file content to a xml variable
			EXECUTE sp_executesql @sql, @ParameterList, @xml = @xml OUTPUT

Open in new window

0
 
elimesikaCommented:
Then, you use this to insert the value to  yourtable.yourfield
INSERT yourtable (yourfield) (
				SELECT  t.c.[value](''@yourxmlattributename'',''nvarchar(100)'') as [Value]
				FROM @xml.nodes(''XPATH expression to your value'')as t(c))

Open in new window

0
 
bob1barkerAuthor Commented:
when I try that I get the following error
Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.).


DECLARE @Link nvarchar(1000), @xml XML, @sql nvarchar(1000), @ParameterList nvarchar(100)
                         SET @ParameterList = '@xml xml OUTPUT'
 set @link = 'http://www.zillow.com/webservice/GetSearchResults.htm?zws-id=X1-ZWz1cwo6lcszyj_aui2x&address=12598+Dupont+Drive&citystatezip=Rancho+Cucamonga+91739'
                        set @sql = 'select * from OpenRowSet(BULK N''' + @Link + ''', Single_Blob) as test'
 
                        --Convert file content to a xml variable
                        EXECUTE sp_executesql @sql, @ParameterList, @xml = @xml OUTPUT
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
elimesikaCommented:
Try to add a standard xml header to your XML

<?xml version="1.0" encoding="utf-8" ?>

Open in new window

0
 
bob1barkerAuthor Commented:
its in the file already
click that link and you should see it
<?xml version="1.0" encoding="utf-8" ?>

its also from another source so I dont have any control over the file
0
 
elimesikaCommented:
Try first to copy the xml to your machine and get to it by file:// instead of http:// 
We have to check that syntax is OK
0
 
bob1barkerAuthor Commented:
works perfect from my local machine
weird
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now