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

score 2 · Answer 1 · answered Aug 29 '13 at 07:52

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>'

select T.N.value('(name/text())[1]', 'nvarchar(100)') as name,
       T.N.value('(value/text())[1]', 'nvarchar(100)') as value
from @XML.nodes('/parameter_set[1]/parameter') as T(N)

XQuery Language Reference (SQL Server)

score 1 · Accepted Answer · answered Aug 29 '13 at 07:45

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

Devart · Answer 3 · 2013-08-29T08:00:54.083

Try this one -

DECLARE @XML XML
SELECT @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>'

SELECT 
      name = t.c.value('name[1]', 'VARCHAR(50)')
    , [type]= t.c.value('type[1]', 'INT')
    , [state]= t.c.value('state[1]', 'INT')
    , value = t.c.value('value[1]', 'INT')
FROM @XML.nodes('parameter_set/parameter') t(c)

Output -

name            type        state       value
--------------- ----------- ----------- -----------
device_index    1           1           1
trx_index       1           1           3

Select multiple tag values of xml in sql

3 Answers3