Return list of all non standard nodes (duplicate) from xml column in sql database-CodePudding

Can you help?

I have a table 'ED' with columns NAME and XMLOUTPUT

Example of the table is

Name              XMLOUTPUT
Person1           Snippet below

XMLOUTPUT is

<Fields>
        <f Name="FIRSTNAME" ColumnOrder="0" SortSequence="-1">
            <FieldWasFlattened>false</FieldWasFlattened>
            <ParentView/>
        </f>
        <f Name="KEYNAME" ColumnOrder="1" SortSequence="-1">
            <FieldWasFlattened>false</FieldWasFlattened>
            <ParentView/>
        </f>
        <f Name="AGE" ColumnOrder="2" SortSequence="-1">
            <FieldWasFlattened>false</FieldWasFlattened>
            <ParentView/>
        </f>
        <f Name="BIRTHDATE" ColumnOrder="3" SortSequence="-1">
            <FieldWasFlattened>false</FieldWasFlattened>
            <ParentView/>
        </f>
        <f Name="LOOKUPID" ColumnOrder="4" SortSequence="-1">
            <FieldWasFlattened>false</FieldWasFlattened>
            <ParentView/>
        </f>
        <f Name="MIDDLENAME" ColumnOrder="5" SortSequence="-1">
            <FieldWasFlattened>false</FieldWasFlattened>
            <ParentView/>
        </f>
        <f Name="Blue (color)" ColumnOrder="6" SortSequence="-1">
            <FieldWasFlattened>false</FieldWasFlattened>
            <ParentView/>
        </f>
          <f Name="Red (color)" ColumnOrder="6" SortSequence="-1">
            <FieldWasFlattened>false</FieldWasFlattened>
            <ParentView/>
        </f>
          <f Name="Yellow (color)" ColumnOrder="6" SortSequence="-1">
            <FieldWasFlattened>false</FieldWasFlattened>
            <ParentView/>
        </f>
          <f Name="Green (color)" ColumnOrder="6" SortSequence="-1">
            <FieldWasFlattened>false</FieldWasFlattened>
            <ParentView/>
        </f>
    </Fields>

What Im trying to do is select the Name column in ED table as well as any Names from the XMLOUTPUT that have (color)

Ideally the output would be

NAME          XMLOUTPUT
Person1       Blue (color)
Person1       Red (color)
Person 1      Yellow (color)
Person1       Green (color)

the closest I have come so far is

select NAME as EDNAME,

    CAST(REPLACE(REPLACE(CONVERT(nvarchar(max),  XMLOUTPUT), '', ''), '', '')
    AS xml).value('(//*:f/@Name) [1] ', 'varchar(50)')
    as Color
    
    from ED
    
    where NAME = 'Person 1'

But this does retrieve but only retrieves the 1st due to the [1] in the statement. Im looking for something that will pull all values where @Name like '%color%'

Thanks for looking.

CodePudding user response：

Please try the following solution.

The XPath XQuery contains() function is very handy for your scenario.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, [Name] VARCHAR(20), XMLOUTPUT XML);
INSERT INTO @tbl ([Name], XMLOUTPUT) VALUES
('Person1', N'<Fields>
    <f Name="FIRSTNAME" ColumnOrder="0" SortSequence="-1">
        <FieldWasFlattened>false</FieldWasFlattened>
        <ParentView/>
    </f>
    <f Name="KEYNAME" ColumnOrder="1" SortSequence="-1">
        <FieldWasFlattened>false</FieldWasFlattened>
        <ParentView/>
    </f>
    <f Name="AGE" ColumnOrder="2" SortSequence="-1">
        <FieldWasFlattened>false</FieldWasFlattened>
        <ParentView/>
    </f>
    <f Name="BIRTHDATE" ColumnOrder="3" SortSequence="-1">
        <FieldWasFlattened>false</FieldWasFlattened>
        <ParentView/>
    </f>
    <f Name="LOOKUPID" ColumnOrder="4" SortSequence="-1">
        <FieldWasFlattened>false</FieldWasFlattened>
        <ParentView/>
    </f>
    <f Name="MIDDLENAME" ColumnOrder="5" SortSequence="-1">
        <FieldWasFlattened>false</FieldWasFlattened>
        <ParentView/>
    </f>
    <f Name="Blue (color)" ColumnOrder="6" SortSequence="-1">
        <FieldWasFlattened>false</FieldWasFlattened>
        <ParentView/>
    </f>
    <f Name="Red (color)" ColumnOrder="6" SortSequence="-1">
        <FieldWasFlattened>false</FieldWasFlattened>
        <ParentView/>
    </f>
    <f Name="Yellow (color)" ColumnOrder="6" SortSequence="-1">
        <FieldWasFlattened>false</FieldWasFlattened>
        <ParentView/>
    </f>
    <f Name="Green (color)" ColumnOrder="6" SortSequence="-1">
        <FieldWasFlattened>false</FieldWasFlattened>
        <ParentView/>
    </f>
</Fields>');
-- DDL and sample data population, end

SELECT ID, [Name]
    ,c.value('@Name', 'VARCHAR(30)') AS Result
FROM @tbl
    CROSS APPLY XMLOUTPUT.nodes('/Fields/f[contains(@Name, "(color)")]') AS t(c);

Output

 ---- --------- ---------------- 
| ID |  Name   |     Result     |
 ---- --------- ---------------- 
|  1 | Person1 | Blue (color)   |
|  1 | Person1 | Red (color)    |
|  1 | Person1 | Yellow (color) |
|  1 | Person1 | Green (color)  |
 ---- --------- ----------------