xml datatype field - SQL Server Forums

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)

xml datatype field

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-05 : 07:20:15

Table tblMyTable has a field myXMLField with datatype xml

Question:

I would like to pull out the values from each record and then group them so that only shows the distict values.
How is this done please?

Thanks

This is what I have at present but it is not correct

select
stringList.value('(/value)')
from
tblMyTable

The records inside the xml field in the table is similar to:

record1 shows:

<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>value1</value>
</stringList>

record2 shows:

NULL

record3 shows:

<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>value5</value>
<value>value2</value>
</stringList>

record4 shows :

<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>value4</value>
<value>value1</value>
<value>value5</value>
<value>value2</value>
</stringList>

RESULT:
value1
value2
value4
value5

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 07:39:24

show your expected output please

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-05 : 07:40:36

Hi,
It s at the buttom of the post.
Here it is again.Thanks

RESULT:
value1
value2
value4
value5

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 11:16:58

[code]DECLARE @Sample TABLE
(
rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
[xml] XML
)

INSERT @Sample
SELECT '<stringList><value>value1</value></stringList>' UNION ALL
SELECT NULL UNION ALL
SELECT '<stringList><value>value5</value><value>value2</value></stringList>' UNION ALL
SELECT '<stringList><value>value4</value><value>value1</value><value>value5</value><value>value2</value></stringList>'

SELECT *
FROM @Sample

SELECT DISTINCT t.c.value('.', 'VARCHAR(20)')
FROM @Sample AS s
CROSS APPLY s.xml.nodes('/stringList/value') AS t(c)[/code]

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-05 : 12:56:37

Hi,
I am not getting results.
Please see below:
Note that I have added to the stringList as first mentioned since every xml record has the following change.
Any thoughts please?
Thanks

DECLARE @Sample TABLE
(
rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
[xml] XML
)

INSERT @Sample
SELECT '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL
SELECT NULL UNION ALL
SELECT '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value2</value></stringList>' UNION ALL
SELECT '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value5</value><value>value2</value></stringList>'

SELECT *
FROM @Sample

SELECT DISTINCT t.c.value('.', 'VARCHAR(20)')
FROM @Sample AS s
CROSS APPLY s.xml.nodes('/stringList/value') AS t(c)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 13:44:27

If you change the sample data, you will need to change the code accordingly!
I am referring to the CROSS APPLY part with the nodes section...

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-05 : 14:38:08

I have now modified it as below.
But there is this Error:
XQuery [@Sample.xml.nodes()]: Syntax error near 'stringList'
Thanks

DECLARE @Sample TABLE
(
rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
[xml] XML
)

INSERT @Sample
SELECT '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL
SELECT NULL UNION ALL
SELECT '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value2</value></stringList>' UNION ALL
SELECT '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value5</value><value>value2</value></stringList>'

SELECT *
FROM @Sample

SELECT DISTINCT t.c.value('.', 'VARCHAR(20)')
FROM @Sample AS s
CROSS APPLY s.xml.nodes('/stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"/value') AS t(c)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 17:33:57

Well... You persist using a xml namespace, so you obviously need to query the sample data using the existing xml namespace.

DECLARE	@Sample TABLE
	(
		rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		[xml] XML
	)

INSERT	@Sample
SELECT	'<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL
SELECT	NULL UNION ALL
SELECT	'<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value2</value></stringList>' UNION ALL
SELECT	'<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value5</value><value>value2</value></stringList>'

SELECT	*
FROM	@Sample

;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS e)
SELECT DISTINCT	t.c.value('.', 'VARCHAR(20)')
FROM		@Sample AS s
CROSS APPLY	s.xml.nodes('/e:stringList/e:value') AS t(c)

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-06 : 03:43:22

Your explanations have made it easier for me to understand the concepts here.
Many thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-06 : 10:41:23

Hi,
Three questions please.
Thanks

1)
How do you use order by in the following query?
;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS e)
SELECT DISTINCT t.c.value('.', 'VARCHAR(20)')
FROM @Sample AS s
CROSS APPLY s.xml.nodes('/e:stringList/e:value') AS t(c)

--?????
order by t.c.value('.', 'VARCHAR(20)')

--

2)
It seems it is not straight forward to insert this data into a table.
Any suggestions please?

declare @table table
{
rowID int identity(1, 1) primary key clustered,
data
}

insert into @data

;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS e)
SELECT DISTINCT t.c.value('.', 'VARCHAR(20)')
FROM @Sample AS s
CROSS APPLY s.xml.nodes('/e:stringList/e:value') AS t(c)

3)
Is there a site here to go through these examples as mentioned above?
Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 11:27:32

quote:
Originally posted by arkiboys

1)
How do you use order by in the following query?

You ORDER BY as with any other resultset

;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS e)
SELECT DISTINCT	t.c.value('.', 'VARCHAR(20)') AS theValue
FROM		@Sample AS s
CROSS APPLY	s.xml.nodes('/e:stringList/e:value') AS t(c)
ORDER BY 	theValue

;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS e)
SELECT DISTINCT	t.c.value('.', 'VARCHAR(20)')
FROM		@Sample AS s
CROSS APPLY	s.xml.nodes('/e:stringList/e:value') AS t(c)
ORDER BY 	1

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 11:29:04

quote:
Originally posted by arkiboys

2)
It seems it is not straight forward to insert this data into a table.

It is as easy as with any other resultset

DECLARE	@Stage TABLE
	(
		rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		data VARCHAR(20)
	)

;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS e)
INSERT		@Stage
		(
			data
		)
SELECT DISTINCT	t.c.value('.', 'VARCHAR(20)') AS theValue
FROM		@Sample AS s
CROSS APPLY	s.xml.nodes('/e:stringList/e:value') AS t(c)
ORDER BY 	theValue

SELECT	*
FROM	@Stage