mardi 5 mai 2015

Selecting all info from nodes with the same name

I'm a total newbie when it comes to xml stuff.

So far I have this piece of xml that I want to extract info from, but all the node names are the same (so it just grabs one of them, unless stated otherwise).

It looks something like this:

<DocumentElement>
  <Screening>
  <ScreeningID>2</ScreeningID>
  <ScreeningDate>2011-09-13T00:00:00-04:00</ScreeningDate>
  <ScreeningResult>1</ScreeningResult>
  <ScreeningResultText>Negative</ScreeningResultText>
  <TextResult>0</TextResult>
  <TextResultText>Not Tested</TextResultText>
  <PageNumber>0</PageNumber>
  <AddedDate>2015-05-03T16:06:41.71774-04:00</AddedDate>
  <UpdateDate>2015-05-03T16:06:41.71774-04:00</UpdateDate>
</Screening>
<Screening>
  <ScreeningID>3</ScreeningID>
  <ScreeningDate>2011-09-13T00:00:00-04:00</ScreeningDate>
  <ScreeningResult>1</ScreeningResult>
  <ScreeningResultText>Negative</ScreeningResultText>
  <TextResult>1</TextResult>
  <TextResultText>Negative</TextResultText>
  <PageNumber>9</PageNumber>
  <AddedDate>2015-05-03T16:25:21.2904988-04:00</AddedDate>
  <UpdateDate>2015-05-03T16:25:21.2904988-04:00</UpdateDate>
</Screening>

And I'm currently using this kind of snippet to extract info from the TextResult area

Select 
answer.value('(/DocumentElement/Screening/TextResult)[1]','int')
From 
Answers

However, that only grabs the first bit of info, I know that if I write something like this, it'll get me the second bit of info but on another column: answer.value('(/DocumentElement/Screening[2]/textResult)[1]','int')

I have two issues with this: 1. There isn't necessarily going to be only 2 nodes with the same name - it could go on infinitely. And 2. I would like all the info to be gathered into only one column.

Any help would be appreciated!

Aucun commentaire:

Enregistrer un commentaire