Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Extract text XML Column MSSQL

Posted on 2011-03-18
9
Medium Priority
?
745 Views
Last Modified: 2012-05-11
Hello
I have a text column in my table with XML tags
I need to extract the text from some nodes
Assume I have the following content
I had the following which works to extract the text from the first description, but how do I extract the text from the second description and insert a "," between the extracted text?

DECLARE @XML AS varchar(MAX)

SET @XML='<root>
<Person>
<Name>john</Name>
<age>14</age>
<description>some description</description>
<description>the description <outsideContent>the content</outsideContent> continued</description>
</Person>
<Person>
<Name>alex</Name>
<age>14</age>
<description>Alexs description</description>
</Person>
</root>
'
SELECT CAST(@XML as XML).value( '(root/Person/description)[1]', ' varchar(2000) ' )

Open in new window

0
Comment
Question by:xav056
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35168812
Try:

DECLARE @XML AS varchar(MAX)

SET @XML='<root>
<Person>
<Name>john</Name>
<age>14</age>
<description>some description</description>
<description>the description <outsideContent>the content</outsideContent> continued</description>
</Person>
<Person>
<Name>alex</Name>
<age>14</age>
<description>Alexs description</description>
</Person>
</root>
'

DECLARE @PARSEDXML as XML
SET @PARSEDXML = CAST(@xml as XML)

SELECT root.person.value('description[1]', 'nvarchar(50)') 
     + ISNULL(',' + root.person.value('description[2]', 'nvarchar(50)'), '') as Description
  FROM @PARSEDXML.nodes('//Person') root(person)

Open in new window


I got the following output:

Description
some description,the description the content continued
Alexs description
0
 
LVL 9

Author Comment

by:xav056
ID: 35168845
this is close but not quite it given the fact that Some times I have 1 description sometimes I have 2 siomet5imes I have three , I do not know the number of description tags its variable
0
 
LVL 9

Author Comment

by:xav056
ID: 35168851
and I could have upo to 10's of description
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 23

Expert Comment

by:wdosanjos
ID: 35168977
OK. Try this then:

DECLARE @XML AS varchar(MAX)

SET @XML='<root>
<Person>
<Name>john</Name>
<age>14</age>
<description>some description</description>
<description>the description <outsideContent>the content</outsideContent> continued</description>
<description>the description 3 <outsideContent>the content 3</outsideContent> continued 3</description>
</Person>
<Person>
<Name>alex</Name>
<age>14</age>
<description>Alexs description</description>
</Person>
</root>
'

DECLARE @PARSEDXML as XML
SET @PARSEDXML = CAST(@xml as XML)

Select Name, Right(dsc, Len(dsc)-1) dsc 
  From (
	SELECT root.person.value('Name[1]', 'nvarchar(50)') Name,
	      (Select ','+dsc.dsc.value('.', 'nvarchar(50)') 
	         From root.person.nodes('description') dsc(dsc) 
	          For XML Path('')) dsc
	  FROM @PARSEDXML.nodes('//Person') root(person)
) a

Open in new window


Here is the output:

Name    dsc
----    -------------------
john	some description,the description the content continued,the description 3 the content 3 continued 3
alex	Alexs description

Open in new window

0
 
LVL 9

Author Comment

by:xav056
ID: 35169055
Seems to work, However I would appreciate it if you can explain the code as it is not obvious to me
and I am wondering if there is a way to exclude the "the content" text which is in the following tag <outsideContent> tag from the result.
Thank you
0
 
LVL 9

Author Comment

by:xav056
ID: 35181774
The above work as long as we have something like set @Xml="the xml string'
What if the xml value is in table?
I am getting errors at this level
FROM @PARSEDXML.nodes('//Person') root(person)
What should be here is a column name from a table
How do I do that?
Thank you
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35182097
The following example pulls the XML from a column. I created a sub-query to illustrate the CAST from varchar to XML.  If the column is already of type XML, the sub-query is not needed.

create table #temp (thexml varchar(max))

insert into #temp values('<root>
<Person>
<Name>john</Name>
<age>14</age>
<description>some description</description>
<description>the description <outsideContent>the content</outsideContent> continued</description>
<description>the description 3 <outsideContent>the content 3</outsideContent> continued 3</description>
</Person>
<Person>
<Name>alex</Name>
<age>14</age>
<description>Alexs description</description>
</Person>
</root>
')

Select Name, Right(dsc, Len(dsc)-1) dsc 
  From (
	SELECT root.person.value('Name[1]', 'nvarchar(50)') Name,
	      (Select ','+dsc.dsc.value('.', 'nvarchar(50)') 
	         From root.person.nodes('description') dsc(dsc) 
	          For XML Path('')) dsc
	  FROM (select cast(thexml as XML) MyXml from #temp) MyTable
	  CROSS APPLY MyTable.MyXml.nodes('//Person') root(person)
) a

Open in new window

0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 total points
ID: 35182364
Removing outsideContent requires two steps, because the modify function only works in updates.  Here is the complete sample (with comments as requested).

create table #MyTable (id int, MyXml nvarchar(max))

insert into #MyTable values(1, '<root>
<Person>
<Name>john</Name>
<age>14</age>
<description>some description</description>
<description>the description <outsideContent>the content</outsideContent> continued</description>
<description>the description 3 <outsideContent>the content 3</outsideContent> continued 3</description>
</Person>
<Person>
<Name>alex</Name>
<age>14</age>
<description>Alexs description</description>
</Person>
<Person>
<Name>alex 2</Name>
<age>14</age>
</Person>
</root>
')
insert into #MyTable values(2, '<root>
<Person>
<Name>2john</Name>
<age>14</age>
<description>some description</description>
<description>the description <outsideContent>the content</outsideContent> continued</description>
<description>the description 3 <outsideContent>the content 3</outsideContent> continued 3</description>
</Person>
<Person>
<Name>2alex</Name>
<age>14</age>
<description>Alexs description</description>
</Person>
<Person>
<Name>2alex 2</Name>
<age>14</age>
</Person>
</root>
')

-- Copy the XML data
select id, CAST(MyXml as XML) MyXml 
  into #temp
  from #MyTable
  
-- Remove the 'outsideContent'
update #temp set MyXml.modify('delete /root/Person/description/outsideContent')

-- Display the description
Select id, Name, Right(dsc, Len(dsc)-1) dsc -- remove the first ,
  From (
	SELECT MyTable.id,
	       root.person.value('Name[1]', 'nvarchar(50)') Name,	-- pulls Person.Name
	      (Select ','+dsc.dsc.value('.', 'nvarchar(50)')		-- pulls Person.description
	         From root.person.nodes('description') dsc(dsc)		-- return 'Person.description' nodes as rows
	          For XML Path('')) dsc -- concatenates all in a single column
	  FROM #temp MyTable
	  CROSS APPLY MyTable.MyXml.nodes('//Person') root(person) -- return 'Person' nodes as rows
) a

-- Clean Up
drop table #temp
drop table #MyTable

Open in new window


More about xml Data Type Methods:
http://msdn.microsoft.com/en-us/library/ms190798.aspx
0
 
LVL 9

Author Comment

by:xav056
ID: 35183590
Thank you
I appreciate it
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question