asked on

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

wdosanjos

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

xav056

ASKER

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

xav056

ASKER

and I could have upo to 10's of description

wdosanjos

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

xav056

ASKER

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

xav056

ASKER

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

wdosanjos

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

ASKER CERTIFIED SOLUTION

wdosanjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xav056

ASKER

Thank you
I appreciate it