Solved

Extract text XML Column MSSQL

Posted on 2011-03-18
9
719 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
Comment Utility
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
Comment Utility
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
Comment Utility
and I could have upo to 10's of description
0
 
LVL 23

Expert Comment

by:wdosanjos
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 9

Author Comment

by:xav056
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you
I appreciate it
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now