Selecting all info from nodes with the same name

Question

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!

Answer 1

I understand your meaning is: get all TextResult in your xml document. If so, you can try this:

string xml = @"<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>
                        </DocumentElement>";
            XElement xmlTree = XElement.Parse(xml);
            IEnumerable<XElement> textResultList = from c in xmlTree.Descendants("TextResult")
                                          select c;
            foreach (var item in textResultList)
            {
                Console.WriteLine(item.Value);
            }

            Console.Read();

I hope this help

Answer 2

You can try this way :

SELECT 
    X.value('.','int') as 'TextResult'
FROM Answers as 'a'
     CROSS APPLY a.answer.nodes('/DocumentElement/Screening/TextResult') as answers(X)

SQL Fiddle