Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 752
  • Last Modified:

Extract text XML Column MSSQL

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
xav056
Asked:
xav056
  • 5
  • 4
1 Solution
 
wdosanjosCommented:
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
 
xav056Author Commented:
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
 
xav056Author Commented:
and I could have upo to 10's of description
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
wdosanjosCommented:
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
 
xav056Author Commented:
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
 
xav056Author Commented:
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
 
wdosanjosCommented:
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
 
wdosanjosCommented:
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
 
xav056Author Commented:
Thank you
I appreciate it
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now