How to get a value into an XML tag in SQL

Question

I don't know how to get a value into an XML tag, in SQL.
For example, my XML is:

<myXML>
  <tag Id="Color" Value="Blue"> Some text </tag>
  <tag Id="Color" Value="Red"> Some text2 </tag>
</myXML>

I know how to put in a table both lines.
I know how to get values "Some text" and "Some text 2".
But I don't know how to get "Red" and "Blue" and found nothing to explain how...

Consider adding more info to the question, currently it's very hard for anyone to answer. What database are you using, what have you tried to so far, why didn't it work etc — Viktor, Aug 23 '19 at 16:05

score 1 · Answer 1 · answered Aug 23 '19 at 16:12

1

Try this option-

DECLARE @DXML XML= 
'<myXML>
    <tag Id="Color" Value="Blue"> Some text </tag>
    <tag Id="Color" Value="Red"> Some text2 </tag>
</myXML>';


SELECT 
T.N.value('@Value', 'varchar(MAX)') AS Color
FROM @dxml.nodes('/myXML/tag') AS T(N)

Output is-

Color
Blue
Red

answered Aug 23 '19 at 16:12

mkRabbani

16,295
2
15
24

I assume, that it is not enough to read the `@Value` and call it `AS Color`... The `Id` attribute seems to point to a *key-value-pair* structure... You might want to read my answer... – Shnugo Aug 26 '19 at 08:47
Thanks for your help – KyaaQx Aug 27 '19 at 14:57
You are most welcome :) – mkRabbani Aug 27 '19 at 15:08

score 0 · Accepted Answer · answered Aug 26 '19 at 08:45

I assume, that your tags are key-value-pairs where Id="Color" is the key, while Value="Blue" is the value.

Try this out:

DECLARE @XML XML= 
'<myXML>
    <tag Id="Color" Value="Blue">Some text</tag>
    <tag Id="Color" Value="Red">Some text2</tag>
    <tag Id="Shape" Value="Rectangle"> Some text3 </tag>
    <tag Id="Width" Value="100">Data-type INT</tag>
</myXML>';

--This statement will return a classical key-value-pairs-list

SELECT tag.value('@Id', 'varchar(max)') AS [Key]
      ,tag.value('@Value', 'varchar(max)') AS [Value]
      ,tag.value('text()[1]','nvarchar(max)') TheContent
FROM @XML.nodes('/myXML/tag') AS A(tag);

--And this will return each value in a dedicated column (which allows typing)

SELECT tag.value('.[@Id="Color"]/@Value', 'varchar(max)') AS Color
      ,tag.value('.[@Id="Shape"]/@Value', 'varchar(max)') AS Shape
      ,tag.value('.[@Id="Width"]/@Value', 'int') AS Width --typed as INT
      ,tag.value('text()[1]','nvarchar(max)') TheContent
FROM @XML.nodes('/myXML/tag') AS A(tag);

I'm a begginer sorry ! It's okay now. – KyaaQx Aug 27 '19 at 15:11 — KyaaQx, Aug 27 '19 at 15:11

How to get a value into an XML tag in SQL

2 Answers2