September 3, 2008 at 12:18 pm
Hi,
I am parsing as XML file created by a PDF program. The XML has a begin and end tag of xfdf that holds the namespace. My parsing logic doesn't work when this tag is there and I haven't figured out an efficient way to strip the tag off the xml. Maybe OPENXML is a better way to go. I need to convert this XML file to a database table so I can map and use the data in some stored procedures.
The file and current parsing select is below. Any help would be greatly appreciated.
thanks,
Maureen
I can't post the xml here for some reason but the tag order is
xfdf
fields
field
value/
/field
/fields
/xfdf
The xfdf tag holds the XML namespace which is adobe.
I am using this select to parse the data.
/* select XML fields into name-value pair for assignment to variables */
(select field.query('data(@name)').value('.', 'varchar(100)') as FieldName
,fields.field.query('value').value('.', 'varchar(100)') as FieldValue
from @OMSA.nodes('/fields/field') fields (field))
Order by FieldName asc
It works great if I manually delete the xfdf tag at the beginning and end of the XML but doesn't return anything if the tags are there. I have tried adding those tags to this select but I can't get it to work.
The XML is too large to convert to nvarchar so I was unable to convert it and parse it with substring also.
I would love to hear other ideas on how to solve this. Once I get this figured out I am over the hump on this project. :w00t:
thanks,
Maureen
September 3, 2008 at 2:23 pm
Post the XML here as an attachment. Don't try to add it to the text of your post.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 2:28 pm
sweetdeal42 (9/3/2008)
/* select XML fields into name-value pair for assignment to variables */(select field.query('data(@name)').value('.', 'varchar(100)') as FieldName
,fields.field.query('value').value('.', 'varchar(100)') as FieldValue
from @OMSA.nodes('/fields/field') fields (field))
Order by FieldName asc
It works great if I manually delete the xfdf tag at the beginning and end of the XML but doesn't return anything if the tags are there.
Looks like your Xquery search values are not taking the root tag into account. How about this:
(select field.query('data(@name)').value('.', 'varchar(100)') as FieldName
,fields.field.query('value').value('.', 'varchar(100)') as FieldValue
from @OMSA.nodes('/xfdf/fields/field') fields (field))
Order by FieldName asc
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 2:28 pm
Thanks. I didn't scroll down far enough before to see the attachment option.
Here is the XML as an attachment.
September 3, 2008 at 2:32 pm
There is no "xfdf" tag in there.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 2:38 pm
Very sorry. I grabbed a sample I had manually altered. Here is a straight file.
Thanks,
Maureen
September 3, 2008 at 3:22 pm
OK, the "xfdf" tag means that first, you have to add another level into your .query to include the root(xfdf) tag. And secondly, the "xfdf" tag is imposing an implicit namespace on all of your tags, so I just wildcarded it in the working example below:
(select field.query('data(@name)').value('.', 'varchar(100)') as FieldName
,fields.field.query('*:value').value('.', 'varchar(100)') as FieldValue
from @OMSA.nodes('/*:xfdf/*:fields/*:field') fields (field))
Order by FieldName asc
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 3, 2008 at 3:41 pm
you are AWESOME!!!!!!!!!!!!!!!!!!!! Thank you. This worked perfectly AND I learned something! Thanks!!!
September 3, 2008 at 3:48 pm
Well, thanks for the feedback! 🙂 Some of the credit goes to Michael Coles SQL XML book, which I have been reading this week. It too is awesome. 😀
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply