Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

reading XML in a loop. Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 1:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 3:22 PM
Points: 4, Visits: 98
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)

Post #1471820
Posted Tuesday, July 9, 2013 1:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 12:10 PM
Points: 13,330, Visits: 12,829
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1471830
Posted Tuesday, July 9, 2013 1:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 3:22 PM
Points: 4, Visits: 98
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....
Post #1471834
Posted Tuesday, July 9, 2013 4:30 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 5:04 PM
Points: 1,817, Visits: 5,918
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1471893
    Posted Tuesday, July 9, 2013 11:32 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, May 6, 2014 3:22 PM
    Points: 4, Visits: 98
    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?
    Post #1471950
    Posted Wednesday, July 10, 2013 12:27 AM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Tuesday, December 23, 2014 5:04 PM
    Points: 1,817, Visits: 5,918
    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('(PID[sql:variable("@LoopCount")]/PID.3)[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 highlighted (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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1471959
    Posted Wednesday, July 10, 2013 12:42 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, May 6, 2014 3:22 PM
    Points: 4, Visits: 98
    Perfect! Much thanks!
    Post #1471966
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse