Search code examples
sqlt-sqlsql-server-openxml

parse openxml and get a name value collection for given node

I have a xml document which contains some custom fields which i wont know the names of. i want to generate a select statement which will list the contents in a name value style.

All examples I have found sofar require me to know the names of the nodes. i.e.

declare @idoc int
     declare @doc nvarchar(max); set 
     @doc = '<user>
  <additionalfields>
    <Account__Manager>Fred Dibner</Account__Manager>
    <First__Aider>St Johns Ambulance</First__Aider>
  </additionalfields>
</user>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
SELECT    * FROM       OPENXML (@idoc, 'user/additionalfields/',1)

is it possible to achieve this?

Solution

  • well i found the answer after a fair amount more experimenting.(incidentally the double underscore replace is due to the output format of some of the database field names.)

    SELECT replace(name,'__',' ') as name, value 
            FROM OPENXML (@idoc, '/user/additionalfields/*',1) 
            WITH (          
                Name  nvarchar(4000) '@mp:localname',
                value  nvarchar(4000) './text()'
                  )

    results