reading XML in a loop.

Question

Post reply

reading XML in a loop.

ethanstout2008

SSC Enthusiast

Points: 190
More actions
July 9, 2013 at 1:23 pm

#295409

I want to read through an xml document with a loop. I've removed the extraneous code.
I get this error: Msg 2389, Level 16, State 1, Line 10
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
When I replace this line:
select a.b.value('PID[position() = sql:variable("@LoopCount")]/PID.3[1]', 'varchar(200)') as tehvalue2
from @XMLDoc.nodes('HL7Message') as a(b)
with this I get expected results.
select a.b.value('PID[1]/PID.3[1]', 'varchar(200)') as tehvalue2
from @XMLDoc.nodes('HL7Message') as a(b)
Declare @LoopCount int
Set @LoopCount = 1
DECLARE @cmd NVARCHAR(MAX),
@rc INT,
@XMLDoc XML
set @XMLDoc = '<?xml version="1.0" encoding="UTF-8"?><HL7Message><MSH><MSH.0>MSH</MSH.0><MSH.1>^~\</MSH.1><MSH.2>GHHLAB</MSH.2><MSH.3>ELAB-3</MSH.3><MSH.4>GHHOE</MSH.4><MSH.5>BLDG4</MSH.5><MSH.6>200202150930</MSH.6><MSH.7></MSH.7><MSH.8><MSH.8.0>ORU</MSH.8.0><MSH.8.1>R01</MSH.8.1></MSH.8><MSH.9>CNTRL-3456</MSH.9><MSH.10>P</MSH.10><MSH.11>2.4</MSH.11></MSH><PID><PID.0>PID</PID.0><PID.1></PID.1><PID.2></PID.2><PID.3>555-44-4444</PID.3><PID.4></PID.4><PID.5><PID.5.0>EVERYWOMAN</PID.5.0><PID.5.1>EVE</PID.5.1><PID.5.2>E</PID.5.2><PID.5.3> </PID.5.3><PID.5.4> </PID.5.4><PID.5.5></PID.5.5><PID.5.6>L</PID.5.6></PID.5><PID.6>JONES</PID.6><PID.7>19620320</PID.7><PID.8>F</PID.8><PID.9> </PID.9><PID.10> </PID.10><PID.11><PID.11.0>153 FERNWOOD DR.</PID.11.0><PID.11.1> </PID.11.1><PID.11.2>STATESVILLE</PID.11.2><PID.11.3>OH</PID.11.3><PID.11.4>35292</PID.11.4></PID.11><PID.12> </PID.12><PID.13>(206)3345232</PID.13><PID.14>(206)752-121</PID.14><PID.15></PID.15><PID.16> </PID.16><PID.17></PID.17><PID.18>2</PID.18><PID.19> </PID.19><PID.20><PID.20.0>67-A4335</PID.20.0><PID.20.1>OH</PID.20.1><PID.20.2>20030520</PID.20.2></PID.20></PID><OBR><OBR.0>OBR</OBR.0><OBR.1>1</OBR.1><OBR.2><OBR.2.0>845439</OBR.2.0><OBR.2.1>GHHOE</OBR.2.1></OBR.2><OBR.3><OBR.3.0>1045813</OBR.3.0><OBR.3.1>GHHLAB</OBR.3.1></OBR.3><OBR.4><OBR.4.0>15545</OBR.4.0><OBR.4.1>GLUCOSE</OBR.4.1></OBR.4><OBR.5> </OBR.5><OBR.6> </OBR.6><OBR.7>200202150730</OBR.7><OBR.8> </OBR.8><OBR.9> </OBR.9><OBR.10> </OBR.10><OBR.11> </OBR.11><OBR.12> </OBR.12><OBR.13> </OBR.13><OBR.14> </OBR.14><OBR.15> </OBR.15><OBR.16><OBR.16.0>555-55-5555</OBR.16.0><OBR.16.1>PRIMARY</OBR.16.1><OBR.16.2>PATRICIAP</OBR.16.2><OBR.16.3> </OBR.16.3><OBR.16.4> </OBR.16.4><OBR.16.5> </OBR.16.5><OBR.16.6>MD</OBR.16.6><OBR.16.7> </OBR.16.7><OBR.16.8> </OBR.16.8></OBR.16><OBR.17> </OBR.17><OBR.18> </OBR.18><OBR.19> </OBR.19><OBR.20> </OBR.20><OBR.21> </OBR.21><OBR.22> </OBR.22><OBR.23> </OBR.23><OBR.24> </OBR.24><OBR.25>F</OBR.25><OBR.26> </OBR.26><OBR.27> </OBR.27><OBR.28> </OBR.28><OBR.29> </OBR.29><OBR.30> </OBR.30><OBR.31><OBR.31.0>444-44-4444</OBR.31.0><OBR.31.1>HIPPOCRATES</OBR.31.1><OBR.31.2>HOWARDH</OBR.31.2><OBR.31.3> </OBR.31.3><OBR.31.4> </OBR.31.4><OBR.31.5> </OBR.31.5><OBR.31.6>MD</OBR.31.6></OBR.31></OBR><OBX><OBX.0>OBX</OBX.0><OBX.1>1</OBX.1><OBX.2>SN</OBX.2><OBX.3><OBX.3.0>1554-5</OBX.3.0><OBX.3.1>GLUCOSE</OBX.3.1><OBX.3.2>POST12HCFST:MCNC:PT:SER/PLAS:QN</OBX.3.2></OBX.3><OBX.4> </OBX.4><OBX.5><OBX.5.0> </OBX.5.0><OBX.5.1>182</OBX.5.1></OBX.5><OBX.6>mg/dl</OBX.6><OBX.7>70_105</OBX.7><OBX.8>H</OBX.8><OBX.9> </OBX.9><OBX.10> </OBX.10><OBX.11>F</OBX.11></OBX><MSH><MSH.0>MSH</MSH.0><MSH.1>^~\</MSH.1><MSH.2>GHHLAB</MSH.2><MSH.3>ELAB-3</MSH.3><MSH.4>GHHOE</MSH.4><MSH.5>BLDG4</MSH.5><MSH.6>200202150930</MSH.6><MSH.7></MSH.7><MSH.8><MSH.8.0>ORU</MSH.8.0><MSH.8.1>R01</MSH.8.1></MSH.8><MSH.9>CNTRL-3456</MSH.9><MSH.10>P</MSH.10><MSH.11>2.4</MSH.11></MSH><PID><PID.0>PID</PID.0><PID.1></PID.1><PID.2></PID.2><PID.3>555-22-4444</PID.3><PID.4></PID.4><PID.5><PID.5.0>EVERYWOMAN</PID.5.0><PID.5.1>EVE</PID.5.1><PID.5.2>E</PID.5.2><PID.5.3> </PID.5.3><PID.5.4> </PID.5.4><PID.5.5></PID.5.5><PID.5.6>L</PID.5.6></PID.5><PID.6>JONES</PID.6><PID.7>19620320</PID.7><PID.8>F</PID.8><PID.9> </PID.9><PID.10> </PID.10><PID.11><PID.11.0>153 FERNWOOD DR.</PID.11.0><PID.11.1> </PID.11.1><PID.11.2>STATESVILLE</PID.11.2><PID.11.3>OH</PID.11.3><PID.11.4>35292</PID.11.4></PID.11><PID.12> </PID.12><PID.13>(206)3345232</PID.13><PID.14>(206)752-121</PID.14><PID.15></PID.15><PID.16> </PID.16><PID.17></PID.17><PID.18>4</PID.18><PID.19> </PID.19><PID.20><PID.20.0>67-A4335</PID.20.0><PID.20.1>OH</PID.20.1><PID.20.2>20030520</PID.20.2></PID.20></PID><OBR><OBR.0>OBR</OBR.0><OBR.1>1</OBR.1><OBR.2><OBR.2.0>845439</OBR.2.0><OBR.2.1>GHHOE</OBR.2.1></OBR.2><OBR.3><OBR.3.0>1045813</OBR.3.0><OBR.3.1>GHHLAB</OBR.3.1></OBR.3><OBR.4><OBR.4.0>15545</OBR.4.0><OBR.4.1>GLUCOSE</OBR.4.1></OBR.4><OBR.5> </OBR.5><OBR.6> </OBR.6><OBR.7>200202150730</OBR.7><OBR.8> </OBR.8><OBR.9> </OBR.9><OBR.10> </OBR.10><OBR.11> </OBR.11><OBR.12> </OBR.12><OBR.13> </OBR.13><OBR.14> </OBR.14><OBR.15> </OBR.15><OBR.16><OBR.16.0>555-55-5555</OBR.16.0><OBR.16.1>PRIMARY</OBR.16.1><OBR.16.2>PATRICIAP</OBR.16.2><OBR.16.3> </OBR.16.3><OBR.16.4> </OBR.16.4><OBR.16.5> </OBR.16.5><OBR.16.6>MD</OBR.16.6><OBR.16.7> </OBR.16.7><OBR.16.8> </OBR.16.8></OBR.16><OBR.17> </OBR.17><OBR.18> </OBR.18><OBR.19> </OBR.19><OBR.20> </OBR.20><OBR.21> </OBR.21><OBR.22> </OBR.22><OBR.23> </OBR.23><OBR.24> </OBR.24><OBR.25>F</OBR.25><OBR.26> </OBR.26><OBR.27> </OBR.27><OBR.28> </OBR.28><OBR.29> </OBR.29><OBR.30> </OBR.30><OBR.31><OBR.31.0>444-44-4444</OBR.31.0><OBR.31.1>HIPPOCRATES</OBR.31.1><OBR.31.2>HOWARDH</OBR.31.2><OBR.31.3> </OBR.31.3><OBR.31.4> </OBR.31.4><OBR.31.5> </OBR.31.5><OBR.31.6>MD</OBR.31.6></OBR.31></OBR><OBX><OBX.0>OBX</OBX.0><OBX.1>1</OBX.1><OBX.2>SN</OBX.2><OBX.3><OBX.3.0>1554-5</OBX.3.0><OBX.3.1>GLUCOSE</OBX.3.1><OBX.3.2>POST12HCFST:MCNC:PT:SER/PLAS:QN</OBX.3.2></OBX.3><OBX.4> </OBX.4><OBX.5><OBX.5.0> </OBX.5.0><OBX.5.1>182</OBX.5.1></OBX.5><OBX.6>mg/dl</OBX.6><OBX.7>70_105</OBX.7><OBX.8>H</OBX.8><OBX.9> </OBX.9><OBX.10> </OBX.10><OBX.11>F</OBX.11></OBX>
</HL7Message>'
select a.b.value('PID[sql:variable("@LoopCount")]/PID.3[2]', 'varchar(200)') as tehvalue2
from @XMLDoc.nodes('HL7Message') as a(b)
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: 286602 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!

reading XML in a loop.

Cookies on SQLServerCentral