bO,
DECLARE @XMLTest XML =
'
<Person>
<Id>87</Id>
<Name>AbacAba</Name>
<Family>wildtype</Family>
<IDNumber>110999</IDNumber>
</person>
<person>
<Id>6</Id>
<Name>AABBc</Name>
<Family>AABBc</Family>
<IDNumber>120000</IDNumber>
</person>
'
SELECT T.Item.value('Id[1]' , 'varchar(20)') As [Id],
T.Item.value('Name[1]' , 'varchar(20)') As [Name],
T.Item.value('Family[1]' , 'varchar(20)') As [Family],
T.Item.value('IDNumber[1]' , 'varchar(20)') As [IDNumber]
FROM @XMLTest.nodes('/Person') AS T(Item)
And the return:
Id Name Family IDNumber
87 AbacAba wildtype 110999
6 AABBc AABBc 120000
Yeah, about that index ... what is it? Possibly some ADO.NET string array identifier? Look at this:
DECLARE @XMLTest XML =
'
<Person>
<Id>87</Id>
<Id>01</Id>
<CulvertNo>6</CulvertNo >
<Name>AbacAba</Name>
<Family>wildtype</Family>
<IDNumber>110999</IDNumber>
</Person>
<Person>
<Id>6</Id>
<Id>02</Id>
<CulvertNo>7</CulvertNo>
<Name>AABBc</Name>
<Family>AABBc</Family>
<IDNumber>120000</IDNumber>
</Person>
'
SELECT T.Item.value('Id[2]' , 'varchar(20)') As [Id],
T.Item.value('Name[1]' , 'varchar(20)') As [Name],
T.Item.value('Family[1]' , 'varchar(20)') As [Family],
T.Item.value('IDNumber[1]' , 'varchar(20)') As [IDNumber],
T.Item.value('CulvertNo[1]' , 'varchar(20)') As [CulvertNo]
FROM @XMLTest.nodes('/Person') AS T(Item)
THAT gets me this:
Id Name Family IDNumber CulvertNo
01 AbacAba wildtype 110999 6
02 AABBc AABBc 120000 7
See the return of the second [Id]. So XQuery has some other nature as well. Less trollish than I imagined.