Select where columnname like ?

[faintfascinatio]

I have an xml file, structure like so:

<people>
  <person.1>
    <fname>brian</fname>
    <lname>smith</lname>
  </person.1>
  <person.11>
    <fname>joe</fname>
    <lname>carey</lname>
  </person.11>
</people>

I am using openxml to read this file and insert names (and other person information) into a table.

DECLARE @person xml, @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @person

INSERT INTO [Person](firstname, lastname)
SELECT firstname = [fname]
FROM OPENXML(@idoc, '//people/person.1',2)
WITH ([fname] varchar(20)
      [lname] varchar(20))

I have a couple different XML files. The different types would always be <person.X>, but depending on the file, it would have a different numbering convention. How would I be able to select all the <person.X> nodes without knowing the number? Is there some sort of a "SELECT WHERE column_name LIKE 'person'" with openxml?

[huangzhi]

May be this query can solve your problem:

DECLARE @person xml, @idoc int, @Local varchar(255), @XML varchar(255)

set @person = '
<ROOT>
<people>  
  <person.1>    
    <fname>brian</fname>
    <lname>smith</lname>
  </person.1>
  <person.11>
    <fname>joe</fname>
    <lname>carey</lname>
  </person.11>
</people>
</ROOT>'

EXEC sp_xml_preparedocument @idoc OUTPUT, @person

select @idoc

declare cs_Parent cursor forward_only for
select LocalName
  FROM OPENXML(@idoc, '//people')
  where parentid = 2
open cs_Parent
fetch next from cs_Parent into @Local
while @@FETCH_STATUS = 0
begin
  select @XML = '//people/' + @Local

  INSERT INTO [Person](firstname, lastname)
    select fname , lname
      from OPENXML(@idoc, @XML, 2)
	   with ([fname] varchar(20), [lname] varchar(20))
		
  fetch next from cs_Parent into @Local
end
close cs_Parent
deallocate cs_Parent
  
EXEC sp_xml_removedocument @idoc

[faintfascinatio]

Sorry, I should've been a little more detailed with my problem..
The code you posted works in that case, but I guess wasn't really what I needed.

Really, I can have xml files like these two:

<typeA>
<people.a>  
  <person.1>    
    <fname.1>brian</fname.1>
    <lname.1>smith</lname.1>
  </person.1>
  <person.11>
    <fname.1>joe</fname.1>
    <lname.1>carey</lname.1>
  </person.11>
</people.a>
</typeA>

<typeB>
<people.b>  
  <person.2>    
    <fname.2>ben</fname.2>
    <lname.2>carr</lname.2>
  </person.2>
  <person.22>
    <fname.2>helen</fname.2>
    <lname.2>montana</lname.2>
  </person.22>
</people.b>
</typeB>

I need to get all the fname and lname of the xmls regardless of type.
The code you posted would work for getting fname.1 and fname.2, but how would I be able to do this for the parent nodes?

I would not be opposed to doing this in c# if it would be easier..