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?