CODEWITHSUNDEEP
sqlxmlt-sql
NoAlias
NoAlias

Reputation: 9193

Insert Into with XML Field to get all Values of a Child Element?

I have a table with an XML Field. The XML Field's Schema is something similar to this...

<Root>
  <Parent>
    <Child>
      <SomeValue>1</SomeValue>
    </Child>
    <Child>
      <SomeValue>1</SomeValue>
    </Child>
  </Parent>

I know how to get the first, second, Nth SomeValue using this...

SELECT      
Child.value('(SomeValue)[1]', 'int')
FROM XMLField.nodes("/Root/Parent/Child[1]") AS N(Child)

I'm trying to use an Insert Into statement to get all of the SomeValue nodes values into a table. The problem is that there could be multiple Child elements per Parent and I only know how to grab one at a time. Is there an easy way of accomplishing this without looping logic? (Each SomeValue value should be its own record in the table I'm inserting into).

Extra Credit: The example I showed gets the first SomeValue value from the first Child element of a single XML Field. Your answer would be extra helpful if you could come up with a solution that not only grabs all of the SomeValue values from a field of a single record, but instead all of the SomeValue values from that field in every record of a table.

Upvotes: 1

Views: 746

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

declare @T table (XMlField xml)

insert into @T values
('<Root>
  <Parent>
    <Child>
      <SomeValue>1</SomeValue>
    </Child>
    <Child>
      <SomeValue>2</SomeValue>
    </Child>
  </Parent>
</Root>')

insert into @T values
('<Root>
  <Parent>
    <Child>
      <SomeValue>3</SomeValue>
    </Child>
    <Child>
      <SomeValue>4</SomeValue>
    </Child>
  </Parent>
</Root>')

select X.N.value('SomeValue[1]', 'int') as SomeValue
from @T as T
  cross apply T.XMLField.nodes('/Root/Parent/Child') as X(N)

Result:

SomeValue
-----------
1
2
3
4

Upvotes: 0

GregM
GregM

Reputation: 2654

This would get you all first SomeValue in a all Child Node : 

SELECT      
N.rows.value('SomeValue[1]', 'int')
FROM XMLField.nodes("/Root/Parent/Child") AS N(rows)

But not sure how to do it if you have 2 SomeValue in the same Child Node

Upvotes: 1

Related Questions