CODEWITHSUNDEEP
sql-serverxmlparsingselectcursor
rjbogz
rjbogz

Reputation: 870

Parsing XML from a select statement

I have a column in my table with XML that looks like this:

<Notes> <Note>
    <Author>test</Author>
    <AuthorInitials>JJJ</AuthorInitials>
    <Contents>Test treatment notes 3</Contents>
    <DateCreated>2013-07-17T14:43:00</DateCreated>
    <DateModified>2013-07-17T14:43:00</DateModified>
</Note>
<Note>
    <Author>test</Author>
    <AuthorInitials>JJJ</AuthorInitials>
    <Contents>This is the intial notes test for tasks</Contents>
    <DateCreated>2013-07-17T14:36:00</DateCreated>
    <DateModified>2013-07-17T14:36:00</DateModified>
</Note>


<Notes> <Note>
    <Author>test</Author>
    <AuthorInitials>JJJ</AuthorInitials>
    <Contents>Test 4 of Task Notes</Contents>
    <DateCreated>2013-07-17T14:57:00</DateCreated>
    <DateModified>2013-07-17T14:57:00</DateModified>
</Note>
<Note>
    <Author>test</Author>
    <AuthorInitials>JJJ</AuthorInitials>
    <Contents>This is the second note test for tasks</Contents>
    <DateCreated>2013-07-17T14:37:00</DateCreated>
    <DateModified>2013-07-17T14:37:00</DateModified>
</Note>

And I want to parse through and just get the <Contents> part of it. Some of the fields have multiple <Contents>, so I need to be able to pull all of them.

My thought was to use a cursor and store the results in a table, but I am still new to SQL Server and I don't think I fully understand them.

Here is what I have currently:

  DECLARE @temptable TABLE
           (
               Category          varchar(5000),
               Notes             varchar(5000)
           )
  DECLARE @Contents     varchar(5000)



  DECLARE c CURSOR FOR SELECT COMMENTS
                    FROM EVENT
                   WHERE COMMENTS <> ''
                     AND COMMENTS IS NOT NULL
                     AND ID = 1171438

    OPEN c

   FETCH NEXT FROM c INTO @Contents

   WHILE (@@FETCH_STATUS = 0)
   BEGIN
      INSERT INTO @temptable (Category, Notes)
      SELECT 'Notes',
             SUBSTRING(COMMENTS,
                       (CHARINDEX('<Contents>',
                                  COMMENTS)+10),
                       (CHARINDEX('</Contents>',
                                  COMMENTS)-CHARINDEX('<Contents>',
                                                                                 COMMENTS)-10))
        FROM Event
       WHERE COMMENTS <> ''
         AND COMMENTS IS NOT NULL
         AND ID = 1171438
       FETCH NEXT FROM c INTO @Contents
   END

   CLOSE c
   DEALLOCATE c

  SELECT *
    FROM @temptable

But this returns only:

Notes | Test treatment notes 3
Notes | Test 4 of Task Notes
Notes | Test treatment notes 3
Notes | Test 4 of Task Notes

Any idea what I'm missing?

EDIT: Solution that worked:

  DECLARE @temptable TABLE
               (
                   Category          varchar(5000),
                   Notes             XML
               )
   INSERT INTO @temptable (Category, Notes)
   SELECT 'Notes',
          COMMENTS
     FROM Event
    WHERE COMMENTS <> ''
      AND COMMENTS IS NOT NULL
      AND ID = 1171438

   SELECT Category,
          Content = XNote.value('(Contents)[1]', 'varchar(5000)')
     FROM @temptable
    CROSS APPLY Notes.nodes('/Notes/Note') AS Xtbl(Xnote)

Upvotes: 1

Views: 2035

Answers (3)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

DECLARE @MyTable TABLE(
    Id INT IDENTITY(1,1) PRIMARY KEY,
    XmlCol XML NOT NULL
);
INSERT @MyTable(XmlCol)
VALUES(N'<Notes> <Note>
    <Author>test</Author>
    <AuthorInitials>JJJ</AuthorInitials>
    <Contents>Test treatment notes 3</Contents>
    <DateCreated>2013-07-17T14:43:00</DateCreated>
    <DateModified>2013-07-17T14:43:00</DateModified>
</Note>
<Note>
    <Author>test</Author>
    <AuthorInitials>JJJ</AuthorInitials>
    <Contents>This is the intial notes test for tasks</Contents>
    <DateCreated>2013-07-17T14:36:00</DateCreated>
    <DateModified>2013-07-17T14:36:00</DateModified>
</Note>
</Notes>');

SELECT  *,
        t.XmlCol.query('
            for $i in (/Notes/Note/Contents)
                return $i
        ') AS AllContent0, -- Only <Contents> elements
        t.XmlCol.query('
            for $i in (/Notes/Note/Contents/text())
                return $i
        ') AS AllContent1, -- Only text without separator
        STUFF(CONVERT(NVARCHAR(4000),t.XmlCol.query('
            for $i in (/Notes/Note/Contents/text())
                return concat(",",$i)
        ')),1,1,'') AS AllContent2 -- Only text with "," as separator
FROM    @MyTable t;

Upvotes: 0

podiluska
podiluska

Reputation: 51494

SQL Server can use XPath/XQuery to extract data from XML. That is a much better idea than what you're doing.

Ideally, you'd store your data as XML and run a query like this.

select 
    id,
    tc.contents.value('.', 'nvarchar(500)')
from yourtable t
    cross apply 
     comments.nodes('/Notes/Note/Contents') as tc(contents)

See http://technet.microsoft.com/en-us/library/ms188282.aspx

If you can't change the data structure (and I strongly advise that you do), you could always do this

select tc.contents.value('.', 'nvarchar(500)')
from (select CONVERT(xml, comments) c from yourtable) t
    cross apply 
     c.nodes('/Notes/Note/Contents') as tc(contents)

Upvotes: 0

marc_s
marc_s

Reputation: 754488

You can use very simple and easy XQuery, IF your column is of type XML in SQL Server.

Try this (also note: you must close the XML with a valid end tag!):

DECLARE @temptable TABLE (ID INT NOT NULL, Notes XML)

INSERT INTO @temptable VALUES(1, '<Notes> <Note>
    <Author>test</Author>
    <AuthorInitials>JJJ</AuthorInitials>
    <Contents>Test treatment notes 3</Contents>
    <DateCreated>2013-07-17T14:43:00</DateCreated>
    <DateModified>2013-07-17T14:43:00</DateModified>
</Note>
<Note>
    <Author>test</Author>
    <AuthorInitials>JJJ</AuthorInitials>
    <Contents>This is the intial notes test for tasks</Contents>
    <DateCreated>2013-07-17T14:36:00</DateCreated>
    <DateModified>2013-07-17T14:36:00</DateModified>
</Note></Notes>'), (2, '<Notes> <Note>
    <Author>test</Author>
    <AuthorInitials>JJJ</AuthorInitials>
    <Contents>Test 4 of Task Notes</Contents>
    <DateCreated>2013-07-17T14:57:00</DateCreated>
    <DateModified>2013-07-17T14:57:00</DateModified>
</Note>
<Note>
    <Author>test</Author>
    <AuthorInitials>JJJ</AuthorInitials>
    <Contents>This is the second note test for tasks</Contents>
    <DateCreated>2013-07-17T14:37:00</DateCreated>
    <DateModified>2013-07-17T14:37:00</DateModified>
</Note></Notes>')

SELECT
    ID,
    Content = XNote.value('(Contents)[1]', 'varchar(100)')
FROM 
    @temptable
CROSS APPLY
    notes.nodes('/Notes/Note') AS XTbl(XNote)

This gives me the output:

ID  Content
1   Test treatment notes 3
1   This is the intial notes test for tasks
2   Test 4 of Task Notes
2   This is the second note test for tasks

There's absolutely no need for a performance- and memory-hogging cursor here!

Upvotes: 1

Related Questions