Solved

Extract text XML Column MSSQL

Posted on 2011-03-18
9
730 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
  • 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
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 500 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

685 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