解析SQL Server表中的XML

Question

我尝试从文件中解析xml。但是结果是空的

XML：

<custom-attributes>
    <custom-attribute attribute-id="color" xml:lang="x-default">BLACK</custom-attribute>
    <custom-attribute attribute-id="color_code" xml:lang="x default">1234</custom-attribute>
</custom-attributes>

<custom-attributes>
    <custom-attribute attribute-id="color_style" xml:lang="x-default">free</custom-attribute>
    <custom-attribute attribute-id="color" xml:lang="x-default">RED</custom-attribute>
    <custom-attribute attribute-id="color_code" xml:lang="x default">1234</custom-attribute>
</custom-attributes>

如何在表中解析？

Color    color_code
BLACK      1234
RED        1234

我尝试了这个查询：

 DECLARE @xml XML = '
<custom-attributes>
            <custom-attribute attribute-id="color" xml:lang="x-default">BLACK</custom-attribute>
            <custom-attribute attribute-id="color_code" xml:lang="x default">1234</custom-attribute>
</custom-attributes>

<custom-attributes>
            <custom-attribute attribute-id="color_style" xml:lang="x-default">free</custom-attribute>
            <custom-attribute attribute-id="color" xml:lang="x-default">RED</custom-attribute>
            <custom-attribute attribute-id="color_code" xml:lang="x default">1234</custom-attribute>
</custom-attributes>'

SELECT 
    Color =   x.t.value('(./custom-attribute)[1]', 'varchar(200)')
FROM
    @xml.nodes('/custom-attributes') AS x(t)

第一列是正确的。但第二个不是。如何修复？

Answer 1

您可以使用query

SELECT 
  Color=x.t.query('(./custom-attribute[@attribute-id="color"]/text())')
 ,Color_code=x.t.query('(./custom-attribute[@attribute-id="color_code"]/text())')
FROM @xml.nodes('/custom-attributes') AS x(t);

Answer 2

您应该尝试：

 Declare @fileData  XML 
             Select @fileData=BulkColumn from OpenRowSet(Bulk'PATH\FILENAME.xml',Single_blob) x;
             select 
                x.xData.value('custom-attribute[@attribute-id="color"][1]','nvarchar(max)') as color,
                x.xData.value('custom-attribute[@attribute-id="color_code"][1]','nvarchar(max)') as color_code,
                x.xData.value('custom-attribute[@attribute-id="color_style"][1]','nvarchar(max)')as color_style
             from @fileData.nodes('/custom-attributes') 
             x(xData);