.query('data(/)') - 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)

.query('data(/)')

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-21 : 08:14:37

I have learned a new way to query an xml field using .query('data(/)')
The good thing about this is that you do not have to use the namespace

The only problem is that if in the xml field there are several values, then the returned result concatenates them first and then display the result

How is it possible to alter this query so that for each value inside the xml field, I get one row.

i.e
declare @myTable table(ProfileID int, Characteristic xml)

insert @myTable

select 1, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Programmes</value></stringList>'
UNION ALL
select 2, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Programmes</value></stringList>'
UNION ALL
select 3, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>VOn</value></stringList>'
UNION ALL
select 4, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>Securities</value><value>VOn</value><value>Notes</value>
</stringList>'

select
convert(nvarchar(max), Characteristic.query('data(/)')) Characteristic,
convert(nvarchar(max), SecurityType.query('data(/)')) SecurityType
from
myTable

Retrieved RESULTS
ProfileID Characteristic
1 Programmes
2 Programmes
3 VOn
4 SecuritiesVOnNotes
...
...

Correct RESULTS shouold be
ProfileID Characteristic
1 Programmes
2 Programmes
3 VOn
4 Securities
4 VOn
4 Notes
...
...

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 08:51:08

for that i think you need to write FLWOR expressions. see below for example

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1309059,00.html

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-21 : 16:02:03

If still using 'data(/)' as before,
Do you know how to modify the query to separate the concatenated values using a comma in the field of the row.

Thanks

So that the result shows something like:
ProfileID Characteristic
1 Programmes
2 Programmes
3 VOn
4 Securities, VOn, Notes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-22 : 01:35:32

Any luck trying the solution i gave here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114935

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-22 : 15:19:32

Hello,
I think you are referring to my other post which is to do with NULL issue. Am I right?
If so, I found that technology difficult to implement.
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-25 : 16:38:50

Hello,
I can not get this query to work for what I want.
This is what I have now:
declare @myTable table(ProfileID int, Characteristic xml)

insert @myTable

select 186990, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Programmes</value></stringList>', NULL
UNION ALL
select 186991, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Programmes</value></stringList>', NULL
UNION ALL
select 186992, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Straights</value></stringList>', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>MTN</value></stringList>'
UNION ALL
select 186993, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Straights</value><value>Von</value><value>Winner</value></stringList>', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>MTN</value><value>something</value></stringList>'

select * from @myTable

SELECT ProfileID ,
Characteristic.query('declare namespace ns=
"http://schemas.myCompany.com/myProject/[app]/stringList/1.0";
for $ed in /stringList/value
return $ed') AS Characteristic
FROM @myTable

ERROR:
Msg 213, Level 16, State 1, Line 3
Insert Error: Column name or number of supplied values does not match table definition.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 16:52:19

1. You are inserting three columns in a two-column table
2. You are declaring the namespace as ns, right? Then your query must also include the namespace name.
But you surely knew this because we have helped you numerous times with this problem now...

declare @myTable table(ProfileID int, Characteristic xml)

insert @myTable

select 186990, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Programmes</value></stringList>'
UNION ALL
select 186991, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Programmes</value></stringList>'
UNION ALL
select 186992, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Straights</value></stringList>' UNION ALL
select 186993, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Straights</value><value>Von</value><value>Winner</value></stringList>'

select * from @myTable

SELECT ProfileID ,
Characteristic.query('declare namespace ns=
"http://schemas.myCompany.com/myProject/[app]/stringList/1.0";
for $ed in /ns:stringList/ns:value
return $ed') AS Characteristic
FROM @myTable

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 17:04:35

[code]declare @myTable table(ProfileID int, Characteristic xml)

insert @myTable

select 186990, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Programmes</value></stringList>'
UNION ALL
select 186991, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Programmes</value></stringList>'
UNION ALL
select 186992, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Straights</value></stringList>' UNION ALL
select 186993, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Straights</value><value>Von</value><value>Winner</value></stringList>'

declare @stage table (profileid int, x varchar(50))

;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS yak)
insert @stage (profileid, x)
SELECT s.ProfileID,
q.c.value('.', 'VARCHAR(50)') AS issuer
FROM @myTable AS s
CROSS APPLY s.Characteristic.nodes('/yak:stringList/yak:value') AS q(c)

select s.profileid,
stuff(r.g, 1, 2, '') AS x
from (select profileid from @stage group by profileid) AS s
cross apply (select ', ' + e.x from @stage AS e where e.profileid = s.profileid for xml path('')) AS r(g)[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 17:09:15

I don't know how we can get through to you.
You ignore most of our working advices and keep trying for other ways.
And when those don't work you come back for more help.

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-26 : 03:12:34

Hi,
Believe it or not I do not try to ignore your help.
Thank you for your help as always.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 03:26:18