XML Parsing Problems

Question

XML Parsing Problems

paul-941374

Say Hey Kid

Points: 701
More actions
November 17, 2008 at 6:35 am

#196148

I am struggling to read the nodes on the following feed:
http://www.slowdating.com/Affiliates/rss.aspx/all.xml
I download it using ASP and then run the following:
DECLARE @docHandle int
DECLARE @xmlFeed XML
SELECT @xmlFeed = BulkColumn FROM OPENROWSET(BULK 'C:\Inetpub\wwwroot\sduk08\feedReader\slowDatingFeedSave.xml', SINGLE_BLOB) AS x
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlFeed
SELECT * FROM OPENXML(@docHandle, 'item', 3) WITH (link VARCHAR(250) 'link/.', title VARCHAR(250) 'title/.')
I think it's something to do with the format of the feed but I'm not sure what or how to get around it. Any help would be much appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

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

winash SSChampion Points: 11446 More actions · Answer 1

Can you attach the XML file so that we can have a look at it? You'll need to attach it as cut-pasting XML in forum posts doesn't work.

paul-941374 Say Hey Kid Points: 701 More actions · Answer 2

paul-941374

Say Hey Kid

Points: 701

November 17, 2008 at 1:24 pm

#899522

Problem download feed attached

winash SSChampion Points: 11446 More actions · Answer 3

I think my brain is tired - I have come up with the code below which might nor might not help you...I'm pretty sure there's a much better way to read nodes from the doc but cannot figure it out right now :doze: ...

I'll relook this later but for now you can check if this helps you in any way...

DECLARE @xmlFeed XML

SET @xmlFeed = (SELECT * FROM OPENROWSET(BULK N'C:\ProblemFeed.xml',SINGLE_CLOB) as xmldata)

SELECT @xmlFeed

SELECT NodeName,NodeValue FROM

(

SELECT

node.value('fn:local-name(.[1])', 'varchar(1000)') AS NodeName,

CAST(node.query('./text()') as varchar(max)) AS NodeValue

FROM @xmlFeed.nodes(N'.//*') T(node)

)X

WHERE NodeName IN ('link','title')

paul-941374 Say Hey Kid Points: 701 More actions · Answer 4

Thanks for your assistance. You've certainly helped me made progress but I'm still not able to select specific nodes. I've done some reading on Xpath and still I've not managed to select all child nodes of "item". As far as I can tell the following should work.

DECLARE @xmlFeed XML

SET @xmlFeed = (SELECT * FROM OPENROWSET(BULK N'C:\ProblemFeed.xml',SINGLE_CLOB) as xmldata)

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlFeed

--SELECT * FROM OPENXML (@docHandle, '//*',0) -- This selects everything fine

SELECT * FROM OPENXML (@docHandle, '//item/*',0) -- As far as I understand this should work

winash SSChampion Points: 11446 More actions · Answer 5

Don't know if this is due to the formatting of the XML...if I strip out a whole bunch of stuff from the XML and use the attached XML your code works fine...

paul-941374 Say Hey Kid Points: 701 More actions · Answer 6

As you say the code seems fine and the problem lies in the XML. The strange thing is, I've managed to select the nodes using ASP. Does this give any more clues on how to select the nodes in tSQL?

<%

dim objXML

Set objXML = Server.CreateObject("MSXML2.DOMDocument")

objXML.async = False

objXML.validateOnParse = false

objXML.resolveExternals = false

objXML.preserveWhiteSpace = false

objXML.setProperty "ServerHTTPRequest", True

if (objXML.Load("http://www.slowdating.com/Affiliates/rss.aspx/all.xml")) then

Set objItemList = objXML.getElementsByTagName("item")

iTotalItems = objItemList.length - 1

response.Write("iTotalItems = " & iTotalItems & "

")

i = 0

For iCount = 0 to iTotalItems

Set objItem = objItemList.item(iCount)

For j = 0 to objItem.childNodes.length - 1

sChildNodeName = objItem.childNodes(j).nodeName

sChildNodeText = objItem.childNodes(j).text

If (sChildNodeName = "link") Then

link = sChildNodeText

response.Write("link = " & link & "

")

ElseIf (sChildNodeName = "title") Then

title = sChildNodeText

response.Write("title = " & title & "

")

ElseIf (sChildNodeName = "description") Then

description = sChildNodeText

response.Write("description = " & description & "

")

ElseIf (sChildNodeName = "dc:date") Then

sdate = sChildNodeText

response.Write("sd:date = " & sdate & "

")

ElseIf (sChildNodeName = "sd:city") Then

city = sChildNodeText

response.Write("sd:city = " & city & "

")

End If

response.write("Error code:" & objXML.parseError.errorCode & " ")

response.write("Error reason:" & objXML.parseError.reason & " ")

end If

set objItemList = nothing

set objXML = nothing

%>

sgmunson SSC Guru Points: 110640 More actions · Answer 7

Based on the VB code you just supplied, this clearly indicates you have a tree structure in the XML that needs to be navigated to determine the appropriate result. I don't happen to know how, but I'm fairly confident that problem is one of navigating the XML tree as opposed to inherent XML formatting. One of the strengths of XML is it's flexibility in representing either heirarchical OR flat data, which is also thus a weakness, in that navigating a tree structure requires additional effort.

Wish I could help, but I would be settling for the ASP piece that works, and doing as much of what needs doing right there as I could. There's bound to be someone on this forum that know XML well enough to both confirm what I thnk is taking place as well as know how to solve it.

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

paul-941374 Say Hey Kid Points: 701 More actions · Answer 8

Thanks for your comments Steve. I could go the ASP route but I'd rather not if at all possible. I'll hold out a little longer in the hope an XML/SQL guru picks up this post.