NULL in xQuery - 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)

NULL in xQuery

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-21 : 08:01:39

This is the xml query I am running:

;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS e)
SELECT
ProfileID,
t.c.value('.', 'VARCHAR(50)') as Characteristic,
t2.c.value('.', 'VARCHAR(50)') as SecurityType
FROM
ProfileMetaDataSummary AS s
CROSS APPLY s.Characteristic.nodes('/e:stringList/e:value') AS t(c)--cross since we definitely want the characteristics...
OUTER APPLY s.SecurityType.nodes('/e:stringList/e:value') AS t2(c)--outer since there may not be a securityType for some profiles...

RESULT:
ProfileID Characteristic SecurityType
186990 Programmes NULL
186991 NULL NULL
186992 system VON
186993 NULL NULL
...
...
...

QUESTION:
There are records for ProfileID 186991 and 186993 AND these records do have values for characteristic and SecurityType fields
BUT I am not sure why NULL is returned.
Do you see what is wrong with the query please?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 08:43:04

can you show how xml looks like. sample two nodes will do

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-21 : 08:56:36

declare @myTable table(ProfileID int, Characteristic xml, SecurityType 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></stringList>', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>MTN</value><value>something</value></stringList>'

The RESULT should show:

186990 Programmes NULL
186991 Programmes NULL
186992 Straights MTN
186993 Straights MTN
186993 Straights Something

Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-21 : 09:33:34

Hi visakh16,
Is the sample data I sent ok?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 09:36:14

Work for me

declare @myTable table(ProfileID int, Characteristic xml, SecurityType 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></stringList>', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>MTN</value><value>something</value></stringList>'

;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS e)
SELECT		ProfileID, 
		t.c.value('.', 'VARCHAR(50)') as Characteristic,
		t2.c.value('.', 'VARCHAR(50)') as SecurityType
FROM		@myTable AS s
CROSS APPLY	s.Characteristic.nodes('/e:stringList/e:value') AS t(c)
OUTER APPLY	s.SecurityType.nodes('/e:stringList/e:value') AS t2(c)

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 09:36:51

Output is

ProfileID	Characteristic	SecurityType
186990		Programmes	NULL
186991		Programmes	NULL
186992		Straights	MTN
186993		Straights	MTN
186993		Straights	something

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 09:39:33

Try like this(not tested)


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, 
SecurityType.query('declare namespace ns=
"http://schemas.myCompany.com/myProject/[app]/stringList/1.0";
for $ed in /stringList/value
return $ed') AS SecurityType 
FROM @myTable

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-21 : 15:57:13

Hello,
Something very strange seems to be happening here.
When the query is run against the main table (Which contains about 20,000 records) then I get the same problem as mentioned before where for some records NULL is returned whereas there are indeed data in those records.
So, I copied the exact values from the records that show NULL in the table with 20,000 records and wrote the following as a test.
When this little test is run, then it works fine.
This is the result that I get when the query is run against the main table:

ProfileID Characteristic SecurityType
201496 Straights MTN
201497 NULL NULL
201498 NULL NULL
201499 NULL NULL
201500 Guaranteed Securities NULL

As a little test, I copied the proper records from the main table and produced this little test. The query against this little test returns the correct data.

What am I doing wrong in the main table with 20,000 records please?
Thanks

Little Test using the same records as in the main table:

declare @myTable table (ProfileID int, SecurityType xml, Characteristic xml)

--populating the test table with the actual values in the main table...
insert @myTable
select 201496, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>MTN</value>
</stringList>', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>Straights</value>
</stringList>'
UNION ALL
select 201497, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>MTN</value>
</stringList>', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>Straights</value>
</stringList>'
UNION ALL
select 201498, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>MTN</value>
</stringList>', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>Straights</value>
</stringList>'
UNION ALL
select 201499, NULL, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>Straights</value>
</stringList>'
UNION ALL
select 201500, NULL, '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0">
<value>Guaranteed Securities</value>
<value>Floating Rate Notes</value>
</stringList>'

;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS e)
SELECT
ProfileID,
t.c.value('.', 'VARCHAR(50)') as Characteristic,
t2.c.value('.', 'VARCHAR(50)') as SecurityType
FROM
@myTable AS s
CROSS APPLY s.Characteristic.nodes('/e:stringList/e:value') AS t(c)
OUTER APPLY s.SecurityType.nodes('/e:stringList/e:value') AS t2(c)
ORDER BY
ProfileID

RESULT:

ProfileID Characteristic SecurityType
201496 Straights MTN
201497 Straights MTN
201498 Straights MTN
201499 Straights NULL
201500 Guaranteed Securities NULL
201500 Floating Rate Notes NULL

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-22 : 15:23:36

Any thoughts on this please?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 02:11:36

[code];WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS e)
SELECT
ProfileID,
t.c.value('.', 'VARCHAR(50)') as Characteristic,
t2.c.value('.', 'VARCHAR(50)') as SecurityType
FROM
@myTable AS s
CROSS APPLY s.Characteristic.nodes('/e:stringList/e:value') AS t(c)
OUTER APPLY s.SecurityType.nodes('/e:stringList/e:value') AS t2(c)
ORDER BY
ProfileID [/code]
gives me
[code]
ProfileID Characteristic SecurityType
201496 Straights MTN
201497 Straights MTN
201498 Straights MTN
201499 Straights NULL
201500 Guaranteed Securities NULL
201500 Floating Rate Notes NULL[/code]

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-24 : 03:04:05

Yes, this is true.
Please read my explanation I send "Posted - 11/21/2008 : 15:57:13 "
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 03:13:25

Do you expect us to replicate the error, without having accecss to your original data? All the 20000 records, your table schema, possible indexes and so on?

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-24 : 03:26:19

Hello,
At present I have solved the issue by doing a combination of cross apply, data(/), temp tables and inner joins.
I guess I should use my existing method instead?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 03:53:14

If it works, go ahead!
We can't tell. We have no data to test on.

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-24 : 06:50:14

Ok,
I guess the only way to find the problem is to send you all the 20,000 records ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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