Select multiple tag values of xml in sql

Question

I have an XML file where I need select value of name and value tag.

<parameter_set>
<parameter>
  <name>device_index</name>
  <type>1</type>
  <state>1</state>
  <value>1</value>
</parameter>
<parameter>
  <name>trx_index</name>
  <type>1</type>
  <state>1</state>
  <value>3</value>
</parameter>
</parameter_set>`enter code here`

I need something like that select name and valueof all paramters

Answer 1

You can use OPENXML for that:

DECLARE @nDoc int
DECLARE @xml xml 

SET @xml = '<parameter_set>
<parameter>
  <name>device_index</name>
  <type>1</type>
  <state>1</state>
  <value>1</value>
</parameter>
<parameter>
  <name>trx_index</name>
  <type>1</type>
  <state>1</state>
  <value>3</value>
</parameter>
</parameter_set>'

EXEC sp_xml_preparedocument @nDoc OUTPUT, @xml

SELECT *
        FROM OPENXML(@nDoc, '/parameter_set/parameter', 1)
        WITH(
            name    nvarchar(MAX)   'name',
            value   nvarchar(MAX)   'value'
        )

EXEC sp_xml_removedocument @nDoc