December 15, 2009 at 7:50 pm
I am trying to extract data from xml files. I have xml like this
<Response>
<Status>Error</Status>
<PONumber>A123456</PONumber>
<PONumber>A789101</PONumber>
</Response>
I am using this:
DECLARE @xml XML
SELECT @xml = xml_data FROM #XmlImportTest
DECLARE @Pointer INT
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml
SELECT
[Status] = Table1.Column1.value('./../Status[1]', 'varchar(50)'),
PONumber = Table1.Column1.value('./../PONumber[1]', 'varchar(24)')
FROM @xml.nodes('/Response/PONumber') AS Table1(Column1)
I am getting this result:
Status PONumber
Error A123456
Error A123456
I am getting same PONumber as first and I need both the numbers something like this.
Status PONumber
Error A123456
Error A789101
Please let me know if there is some way to get this query results.
Thanks in advance for help.
December 21, 2009 at 9:38 am
Two things, first in your SQL script:
DECLARE @xml XML
SELECT @xml = xml_data FROM #XmlImportTest
DECLARE @Pointer INT
EXECUTE sp_xml_preparedocument @Pointer OUTPUT,@xml
SELECT
[Status] = Table1.Column1.value('./../Status[1]', 'varchar(50)'),
PONumber = Table1.Column1.value('./../PONumber[1]', 'varchar(24)')
FROM @xml.nodes('/Response/PONumber') AS Table1(Column1)
You do not need "[font="Courier New"]sp_xml_preparedocument[/font]" anymore, XML data types and the XML functions ([font="Courier New"].node()[/font], etc..) supersede it, so it and your @Pointer aren't doing anything for you except slowing it down. Still, that is not what is causing your problem.
Secondly, your .nodes() call [font="Courier New"]('/Response/PONumber')[/font] is returning a current-pointer at every instance of <PONumber>, so far, so good.
Your [Status] column is coming from the function call .[font="Courier New"]value('./../Status[1]', ...)[/font], which tells it to 1) go up to the parent of the current-pointer ('./..') and then 2) find the first <Status> child element under it ('/Status[1]') which is just what you want and always returns the value "Error". Again, so far so good...
However, you use the same syntax for your [PONumber] column: [font="Courier New"].value('./../PONumber[1]', ...)[/font] which again tells it to 1) go up to the parent of the current-pointer ('./..') and then 2) find the first <PONumber> child element under it ('/PONumber[b]1[/b]') which is always returns the value "A123456", not at all what you want.
The problem is in the part (1), "go up to the parent element of the current-pointer" as this effectively destroys your current-pointer context, which was already pointing at exactly the <PONumber> element that you wanted. To fix this, you use the much simpler expression [font="Courier New"].value('.', [/font]...[font="Courier New"])[/font] which simply says to "return the value at the current-pointer".
So your query should look like this:
SELECT
[Status] = Table1.Column1.value('./../Status[1]', 'varchar(50)'),
PONumber = Table1.Column1.value('.', 'varchar(24)')
FROM @xml.nodes('/Response/PONumber') AS Table1(Column1)
[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]
December 22, 2009 at 8:29 am
Great! Thanks for the detailed response.
I was trying pointer to check if can be done with OPENXML but I like your solution.
Thanks,
SQLArch
December 23, 2009 at 4:05 pm
Glad I could help.
[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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply