1

I have the following dataset:

Index      | xml
A          | <book><name>A book 1</name><book>
B          | <book><name>B book 1</name><book><book><name>B book 2</name><book>

And have been working with this code:

SELECT  [Index],
      CAST([BookResults].query('data(/Book/Name)') AS VARCHAR(max)) AS BookName,
 FROM [BookCache]

What I need in the end is this table: Index | BookName

A         | A Book 1 
B         | B Book 1 
B         | B Book 2

What I seem to be getting with this query is: 

A         | A Book 1 
B         | B Book 1 B Book 2

Where am I going wrong? How do I separate out the fields?

Volearix
  • 1,573
  • 3
  • 23
  • 49

1 Answers1

1

How this works, I have no idea. I'm terrible at XML. I just looked at some examples and threw it together. Hope this helps!

DECLARE @BookCache TABLE ([Index] CHAR,[xml] XML);
INSERT INTO @BookCache
VALUES  ('A','<book><name>A book 1</name></book>'),
        ('B','<book><name>B book 1</name></book><book><name>B book 2</name></book>');

SELECT  [Index],
        BookNameNode.value('(text())[1]','VARCHAR(25)') AS Name
FROM @BookCache
CROSS APPLY [Xml].nodes('/book/name') AS [test](BookNameNode)

Results:

Index  Name
-----  -------
A      A book 1
B      B book 1
B      B book 2
Stephan
  • 5,891
  • 1
  • 16
  • 24
  • Can you provide where you grabbed this from!? This is exactly what I've been searching for! Google seemed to just bring me a bunch of garbage! – Volearix May 06 '15 at 15:11
  • 1
    Haha :) I got the example from the accepted answer: http://stackoverflow.com/questions/10344553/how-can-i-query-a-value-in-sql-server-xml-column – Stephan May 06 '15 at 15:16