All elements named "X" from entire XML

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

All elements named "X" from entire XML

Author

Topic

simsim
Starting Member

3 Posts

Posted - 2009-06-04 : 06:19:36

Hello,

Apologies, I'm sure this is probably straight-forward, but my TSQL XML knowledge is a little shaky.

I am trying to find all the elements called "Comment" from an XML column in a database for a particular row. The comment elements can appear (nearly) anywhere in the XML, however I need to get all of them to check if they contain certain keywords (like 'URGENT'). This is what I've come up with so far:


SELECT
ISNULL(SOME_XML.value('(//*[local-name()="Comment"])[1]','varchar(255)'),'') +
ISNULL(SOME_XML.value('(//*[local-name()="Comment"])[2]','varchar(255)'),'') +
ISNULL(SOME_XML.value('(//*[local-name()="Comment"])[3]','varchar(255)'),'') +
ISNULL(SOME_XML.value('(//*[local-name()="Comment"])[4]','varchar(255)'),'') +
ISNULL(SOME_XML.value('(//*[local-name()="Comment"])[5]','varchar(255)'),'') +
ISNULL(SOME_XML.value('(//*[local-name()="Comment"])[6]','varchar(255)'),'') +
ISNULL(SOME_XML.value('(//*[local-name()="Comment"])[7]','varchar(255)'),'') +
ISNULL(SOME_XML.value('(//*[local-name()="Comment"])','varchar(255)'),'') +
ISNULL(SOME_XML.value('(//*[local-name()="Comment"])[9]','varchar(255)'),'') +
ISNULL(SOME_XML.value('(//*[local-name()="Comment"])[10]','varchar(255)'),'')
FROM SOME_TABLE_WITH_XML
WHERE ID=1234567891011

(the eightball above should be [_8_] without underscores ;) )

Although this is ugly, it gives a result I can use, provided there are less than 10 comments in the XML (possibly a reasonable assumption). But can I return all of the comment fields, regardless of how many there are? There is no requirement that the result strings have to be added together like this, I can also use a result set with the comments seperate rows.

Any help given would be very much apprecieated. Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 06:27:10

You can have up to 10 elements, named Comment, at same node?

<root>
<comment>first</comment>
<comment>second</comment>
</root>

E 12°55'05.63"
N 56°04'39.26"

simsim
Starting Member

3 Posts

Posted - 2009-06-04 : 06:33:05

The comment fields can appear anywhere in the XML e.g.:

<root>
<elementGroup>
<element>
<stuff>
<comment>Stuff comment</comment>
</stuff>
<comment>Element comment</comment>
</element>
<comment>Element group comment</comment>
</elementGroup>
<comment>Root comment</comment>
</root>

The //*[local-name()="Comment"] in the XML value query finds them all, but I want to get any number, not just 10.

simsim
Starting Member

3 Posts

Posted - 2009-06-04 : 06:55:18

Oops! I stumbled across a possible solution for what I need. I think this gives the result I was looking for:


SELECT LOC.value('.','varchar(255)') AS COMMENT
FROM SOME_TABLE_WITH_XML
CROSS APPLY SOME_XML.nodes('//*[local-name()="Comment"]') AS T(LOC)
WHERE ID=1234567891011

Testing it with some data seems to return the correct result (i.e. all the elements called "comment" in the XML). Sorry for wasting anyone's time!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 07:02:55

Just remember that XML is case sensitive.
In your case you have to write "comment" instead of "Comment" in your CROSS APPLY statement.

Thank you for posting your solution here.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 07:07:23

If you also want to get the ParentNodeName which the current Comment node is placed under, try this

DECLARE @Data XML

SET @Data = '	<root>
			<elementGroup>
				<element>
					<stuff>
						<comment>Stuff comment</comment>
					</stuff>
					<comment>Element comment</comment>
				</element>
				<comment>Element group comment</comment>
			</elementGroup>
			<comment>Root comment</comment>
		</root>'

SELECT	e.value('local-name(..)[1]', 'VARCHAR(MAX)') AS ParentNodeName,
	e.value('.', 'VARCHAR(MAX)') AS Comment
FROM	@data.nodes('//*[local-name(.) = "comment"]') AS n(e)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources