?
Solved

Extract text XML Column MSSQL

Posted on 2011-03-18
9
Medium Priority
?
742 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

762 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