Parse XML in SQL Server table

Question

I trying parse xml from file. But result is empty

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>

How to parse in table?

Color    color_code
BLACK      1234
RED        1234

I tried this query:

 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)

First column is correct. But second is not. How to fix?

Lukasz Szozda · Accepted Answer · 2018-10-27 17:04:13Z

2

You could use 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);

db<>fiddle demo

edited Oct 27, 2018 at 17:04

answered Oct 27, 2018 at 17:02

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Helgato · Accepted Answer · 2018-10-27 17:05:50Z

1

You should try :

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

answered Oct 27, 2018 at 17:05

Helgato

1515 bronze badges

Collectives™ on Stack Overflow

Parse XML in SQL Server table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related