從sql資料庫中的xml列回傳所有非標準節點（重復）的串列

你能幫我嗎？

我有一個包含列 NAME 和 XMLOUTPUT 的表“ED”

表的例子是

Name              XMLOUTPUT
Person1           Snippet below

XMLOUTPUT 是

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

我想要做的是選擇 ED 表中的 Name 列以及 XMLOUTPUT 中具有（顏色）的任何名稱

理想情況下，輸出將是

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

到目前為止我最接近的是

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' 

但這確實檢索但由于陳述句中的 [1] 而僅檢索第一個。我正在尋找可以將所有值拉到@Name 中的東西，例如 '%color%'

謝謝你看。

uj5u.com熱心網友回復：

請嘗試以下解決方案。

XPath XQuerycontains()函式對于您的場景非常方便。

查詢陳述句

-- 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);

輸出

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

標籤：sql xml

上一篇：回圈的XSLT排序不起作用

下一篇：XPath在多個級別具有多個條件？