reading XML in a loop.

Question

Post reply

reading XML in a loop.

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

Is there a question here or are you simply explaining how you solved this?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ethanstout2008 SSC Enthusiast Points: 190 More actions · Answer 2

I'd like to know how to implement the loopcount variable so I can read through each value. Sorry and thanks for the help. The first time should grab the first person's SS#, the second time through the loops should get the second...etc....

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

You just need to make it into a singleton query by plopping some brackets round the selector...

select a.b.value('(PID[sql:variable("@LoopCount")]/PID.3)[1]', 'varchar(200)') as tehvalue2

from @XMLDoc.nodes('HL7Message') as a(b)

...but why are you using a loop? if you explain what you are trying to achieve, maybe we can help get rid of that loop?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ethanstout2008 SSC Enthusiast Points: 190 More actions · Answer 4

This is going to call other queries depending on the data in the xml. Not all of the values that determine the will be the same. For example 2 bits of xml could call query 1 and another could call a second query. So when you say put brackets around it, I though I already had? Can you specifically show what you mean?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

ethanstout2008 (7/9/2013)
Can you specifically show what you mean?

I did 😀

mister.magoo (7/9/2013)
You just need to make it into a singleton query by plopping some brackets round the selector...
select a.b.value('[highlight="#ffff11"]([/highlight]PID[sql:variable("@LoopCount")]/PID.3[highlight="#ffff11"])[/highlight][1]', 'varchar(200)') as tehvalue2
from @XMLDoc.nodes('HL7Message') as a(b)

To explain more, this:

PID[sql:variable("@LoopCount")]/PID.3[1]

is not a singleton query because (as far as the engine knows) there could be more than 1 PID that matches whatever is in the variable @LoopCount.

The fact you put "[1]" after the "PID.3" selector just means "the first PID.3 node which is a child of any PID node previously selected", so you need the brackets I have now [highlight="#ffff11"]highlighted [/highlight] (but were in my first response already) to make the xpath query into a singleton query.

Hope that explains a bit better for you 🙂

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

ethanstout2008 SSC Enthusiast Points: 190 More actions · Answer 6

ethanstout2008

SSC Enthusiast

Points: 190

July 10, 2013 at 12:42 am

#1631079

Perfect! Much thanks!