Read XML element from XML Column

Question

I have a XML column in a table with values like

<m lingua="1">  
  <coloriVini>  
    <i n="8" />  
    <i n="2" />  
    <i n="3" />  
    <i n="4" />  
    <i n="5" />  
    <i n="6" />  
    <i n="7" />  
  </coloriVini>  
</m>

and I need to make a join with values from /m/coloriVini/i/@n with another table with will be displayed comma separated , but this I can handle

One idea is to make a variable and store the XML, but I am asking you if I can do it easily

I've tried

SELECT [L].[nome]
, p.value('./coloriVini', 'xml')
FROM [dbo].[contatto] C
CROSS APPLY [xmlMailing].nodes('m') t(p)
LEFT JOIN [dbo].[lingua] L ON p.value('./@lingua', 'int') = L.id

I know p.value('./coloriVini', 'xml') or p.value('./coloriVini', 'nvarchar(200)') doesn't work, can you tell me what I'm doing wrong
Thanks

Devart · Accepted Answer · 2013-04-17 06:20:16Z

1

Try this one -

DECLARE @XML XML
SELECT @XML = 
'<m lingua="1">  
  <coloriVini>  
    <i n="8" />  
    <i n="2" />  
    <i n="3" />  
    <i n="4" />  
    <i n="5" />  
    <i n="6" />  
    <i n="7" />  
  </coloriVini>  
</m>'

DECLARE @lingua TABLE
(
      id INT
    , nome CHAR(1)
)

INSERT INTO @lingua (id, nome)
VALUES 
    (8, '8'),
    (2, '2')

SELECT 
      l.nome
    , x.coloriVini
FROM (
    SELECT 
          id = t.p.value('@n', 'int') 
        , coloriVini = t.p.query('../.')
    FROM @XML.nodes('/m/coloriVini/i') t(p)
) x
JOIN @lingua l ON l.id = x.id

answered Apr 17, 2013 at 6:20

Devart

120k23 gold badges172 silver badges189 bronze badges

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

Collectives™ on Stack Overflow

Read XML element from XML Column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related