SQL Server : get the node values from xml, but node names are the same-CodePudding

I have a weird question. Is it possible to get the attval node value filtered by attrno node from the XML shown below?

Example: I need to get the attval value where attrno is 12 and 2:

<attributes>
    <attribute>
        <attrno>2</attrno>
        <attval>NA</attval>
    </attribute>
    <attribute>
        <attrno>11</attrno>
        <attval>0123456</attval>
    </attribute>
    <attribute>
        <attrno>12</attrno>
        <attval>2210</attval>
    </attribute>
    <attribute>
        <attrno>10</attrno>
        <attval>0.049833</attval>
    </attribute>
    <attribute>
        <attrno>11</attrno>
        <attval>EA</attval>
    </attribute>
    <attribute>
        <attrno>12</attrno>
        <attval>EUR</attval>
    </attribute>
</attributes>

This is the T-SQL query I am trying to use:

INSERT INTO @temp (id, skid, Attval)
    SELECT      
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        col.value('(../../tcid/text())[1]', 'VARCHAR(200)'),
        col.value('(attval/text())[1]', 'VARCHAR(200)')
    FROM
        @DataRecXML.nodes('/rec/tcids/tcid_rec/attributes/attribute') AS tb(col)
    WHERE 
        col.value('(attrno/text())[1]', 'VARCHAR(200)') = 2
        OR col.value('(attrno/text())[1]', 'VARCHAR(200)') = 12
        AND col.value ('(attval/text())[1]', 'VARCHAR(200)') <> 'EUR'

Thank you for any help :)

CodePudding user response：

Too long for a comment. This is a sample modelling the requested behaviour. What is wrong with the output?

select id, 
   a.n.value('attrno[1]', 'int') attrno,
   a.n.value('attval[1]', 'varchar(20)') attval
from  -- your table here
   ( select 1 id, cast(
'<attributes>
          <attribute>
            <attrno>2</attrno>
            <attval>NA</attval>
          </attribute>
          <attribute>
            <attrno>11</attrno>
            <attval>0123456</attval>
          </attribute>
          <attribute>
            <attrno>12</attrno>
            <attval>2210</attval>
          </attribute>
          <attribute>
            <attrno>10</attrno>
            <attval>0.049833</attval>
          </attribute>
          <attribute>
            <attrno>11</attrno>
            <attval>EA</attval>
          </attribute>
          <attribute>
            <attrno>12</attrno>
            <attval>EUR</attval>
          </attribute>
        </attributes>' 
          as xml) xcol 
) tbl
cross apply xcol.nodes('attributes/attribute') a(n)
where a.n.value('attrno[1]', 'int') = 2 
     or a.n.value('attrno[1]', 'int') = 12 and a.n.value('attval[1]', 'varchar(20)') <> 'EUR'

Output

id  attrno  attval
1   2   NA
1   12  2210

CodePudding user response：

Please try the following solution.

The filters are applied in the XPath predicates. It makes code more compact and performant.

SQL

DECLARE @DataRecXML XML =
N'<attributes>
    <attribute>
        <attrno>2</attrno>
        <attval>NA</attval>
    </attribute>
    <attribute>
        <attrno>11</attrno>
        <attval>0123456</attval>
    </attribute>
    <attribute>
        <attrno>12</attrno>
        <attval>2210</attval>
    </attribute>
    <attribute>
        <attrno>10</attrno>
        <attval>0.049833</attval>
    </attribute>
    <attribute>
        <attrno>11</attrno>
        <attval>EA</attval>
    </attribute>
    <attribute>
        <attrno>12</attrno>
        <attval>EUR</attval>
    </attribute>
</attributes>';

 SELECT id = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        attrno = col.value('(attrno/text())[1]', 'INT'),
        attval = col.value('(attval/text())[1]', 'VARCHAR(200)')
FROM @DataRecXML.nodes('/attributes/attribute[(attrno/text())[1]=("2","12") 
    and (attval/text())[1] ne "EUR"]') AS tb(col);

Output

 ---- -------- -------- 
| id | attrno | attval |
 ---- -------- -------- 
|  1 |      2 | NA     |
|  2 |     12 | 2210   |
 ---- -------- --------