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)

xQuery

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-19 : 17:41:15

HI,
This query returns the correct count for the field I am selecting but I am not sure why Null is also returned as one of the fields with a high count.
Please note that there is no null in the field I am selecting.

I can easily add to the where clause i.e. where Issuer is not null
but I do not believe that I should have to so this because as mentioned below, there are no null values in the field at all.

Do you know how to modify this query please?

;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS e)
select
Issuer,
count(*) as [Count]
from
(
SELECT
t.c.value('.', 'VARCHAR(50)') as Issuer
FROM
Profile AS s
cross APPLY s.Issuer.nodes('/e:stringList/e:value') AS t(c)
where
LastModifiedBy = 'LegacyImport' ) as d
--where Issuer is not null
group by
Issuer
order by
Issuer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 01:35:07

show you xml plaese

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-20 : 02:52:03

I have done further checks.
There seems to be something wrong with the xQuery I am executing.
In the sql code below note that there where clause shows profileID = 18991.
This returns a record as follows:

ProfileID Characteristic SecurityType
18991 Debts NULL

However, if I comment out the line where ProfileID = 18991 then the following is returned.

ProfileID Characteristic SecurityType
18991 NULL NULL

I think if I work out why this is the case in the below query, then I can work out the question to my first post.

Question:
So, do you see why I get null returned.
There is definitely data as explained above.
xml sample is shown right at the end.

;WITH XMLNAMESPACES('http://schemas.myCompany.com/myProject/[app]/stringList/1.0' AS e)
select
d.ProfileID,
d.Characteristic,
d.SecurityType
from
(
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...

) as d
--where
--ProfileID = 186991
group by
d.ProfileID,
d.Characteristic,
d.SecurityType
order by
ProfileID

------XML sample as you asked

The table has alot of records.
The ProfileID I am investigating has the following data...
This is the data inside the characteristic field of the table

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

SecurityType field is empty

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2008-11-20 : 07:51:58

I have now solved this problem by using query(/)
Thanks anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources