Problem with ampersand "&"

I’m using XQUERY to slog through an XML doc, and dump the value of a CDATA
element into an NVARCHAR(MAX) field. The problem I’m running into is that
when an ampersand goes into that field, what I get back out in a query (using
FOR XML) is “&”.
using:
CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
The above stores a “&” in the field, even though the input was:
<![CDATA[&]>
Any insight is much appreciated.
Looks like my above post got mangled.
The value it stores is: "&"
And the value I get back is: "&amp;"
|||"Rob Epler" <RobEpler@.discussions.microsoft.com> wrote in message
news:1E572252-FD0D-4395-868F-504CA84D6C40@.microsoft.com...
> Looks like my above post got mangled.
> The value it stores is: "&"
> And the value I get back is: "&amp;"
This double escaping often happens when the source is already escaped but
marked as plain text, e.g. in a CDATA section. Can you show an example of
the source?

Joe Fawcett - XML MVP
http://joe.fawcett.name
|||Hello Joe,
The XMLRW parser seems to be discarding the CDATA markup around the value
when storing nodes like this. What Rob is storing is something like '<Value><![CDATA[&]]></Value>'.
In SQL Server 2005, at serialization time, all element text-nodes are entitized.
This seems to superceed what the spec seems to indicate should be done with
CDATA at parse time.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Hi Rob
sorry for the late reply.
But you should never extract values from an XML document using
CONVERT(nvarchar(MAX), T.c.query('data(Value)'))
use
T.c.value('Value', 'nvarchar(MAX)') instead. That should take care of your
encoding issue.
Best regards
Michael
"Rob Epler" <Rob Epler@.discussions.microsoft.com> wrote in message
news:BFFD350A-2B24-4133-8CEB-FCF5F700D560@.microsoft.com...
> I'm using XQUERY to slog through an XML doc, and dump the value of a CDATA
> element into an NVARCHAR(MAX) field. The problem I'm running into is that
> when an ampersand goes into that field, what I get back out in a query
> (using
> FOR XML) is "&".
> using:
> CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
> The above stores a "&" in the field, even though the input was:
> <![CDATA[&]>
> Any insight is much appreciated.
>

Problem with ampersand "&amp;"

I’m using XQUERY to slog through an XML doc, and dump the value of a CDATA
element into an NVARCHAR(MAX) field. The problem I’m running into is that
when an ampersand goes into that field, what I get back out in a query (usin
g
FOR XML) is “&”.
using:
CONVERT( nvarchar(MAX), T.c.query('data(Value)') )
The above stores a “&” in the field, even though the input was:
<![CDATA[&]>
Any insight is much appreciated.Looks like my above post got mangled.
The value it stores is: "&"
And the value I get back is: "&"|||"Rob Epler" <RobEpler@.discussions.microsoft.com> wrote in message
news:1E572252-FD0D-4395-868F-504CA84D6C40@.microsoft.com...
> Looks like my above post got mangled.
> The value it stores is: "&"
> And the value I get back is: "&"
This double escaping often happens when the source is already escaped but
marked as plain text, e.g. in a CDATA section. Can you show an example of
the source?
Joe Fawcett - XML MVP
http://joe.fawcett.name|||Hello Joe,
The XMLRW parser seems to be discarding the CDATA markup around the value
when storing nodes like this. What Rob is storing is something like '<Value>
<![CDATA[&]]></Value>'.
In SQL Server 2005, at serialization time, all element text-nodes are entiti
zed.
This seems to superceed what the spec seems to indicate should be done with
CDATA at parse time.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/sql

Problem with a query using MAX(ID)

I have a query regarding and I think I need to use max ID to get round it.I am creating a query and I need to get the Home Telephone number (from table 3).The problem is that there may be more than one home telephone nuber so I want to get the lastest one (highest ID).

I need to display all the person id from table along with their home no even if it is null.I’m not sure how to get round it whether I do a subquery joining the tables or whether I do a select case.

Please help

Table 1

Person ID

111

112

113

114

207

Table 2

PersonIdID

1110122

1110123

1120124

1130125

2070126

2070127

Table 3

IDTel_noType

01220125 23223Hone

01230122 43533Home

01240122 444111Mobile

0125077747474Mobile

012601222747474Home

012701232484848Home

Result

Person IDHome No

1110122 43533

112NULL

113NULL

114NULL

20701232 484848

use the following query...

Code Snippet

Create Table #table1 (

[PersonId] Int

);

Insert Into #table1 Values('111');

Insert Into #table1 Values('112');

Insert Into #table1 Values('113');

Insert Into #table1 Values('114');

Insert Into #table1 Values('207');

Create Table #table2 (

[PersonId] int ,

[ID] int

);

Insert Into #table2 Values('111','0122');

Insert Into #table2 Values('111','0123');

Insert Into #table2 Values('112','0124');

Insert Into #table2 Values('113','0125');

Insert Into #table2 Values('207','0126');

Insert Into #table2 Values('207','0127');

Create Table #table3 (

[ID] int ,

[Tel_no] Varchar(100) ,

[Type] Varchar(100)

);

Insert Into #table3 Values('0122','0125 23223','Hone');

Insert Into #table3 Values('0123','0122 43533','Home');

Insert Into #table3 Values('0124','0122 444111','Mobile');

Insert Into #table3 Values('0125','0777 47474','Mobile');

Insert Into #table3 Values('0126','01222 747474','Home');

Insert Into #table3 Values('0127','01232 484848','Home');

Select

X.PersonId

,Tel_No

,Y.ID

,Type Into #Temp

From

#Table1 X

Left Outer Join #Table2 Y On X.Personid=Y.PersonID

Left Outer Join #Table3 Z on Z.Id=Y.ID

Select

A.PersonId

,B.Tel_No as Home_No

from

(Select PersonId, Max(Case When Type='Home' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

Select

A.PersonId

,B.Tel_No as Mobile_No

from

(Select PersonId, Max(Case When Type='Mobile' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

Select Home.PersonId,Home_No,Mobile_No from

(

Select

A.PersonId

,B.Tel_No as Home_No

from

(Select PersonId, Max(Case When Type='Home' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

) as Home

Join

(

Select

A.PersonId

,B.Tel_No as Mobile_No

from

(Select PersonId, Max(Case When Type='Mobile' Then Id Else NULL END) ID

From #Temp Group By PersonId) A

Left Outer Join #Temp B On A.PersonId=B.PersonId And A.id=B.ID

) as Mobile

On Home.PersonId=Mobile.PersonId

|||Here are two versions, one for SQL Server 2005 and later, and one for SQL Server 2000 and earlier:

-- Requires SQL Server 2005 or later
with TPRanked as (
select
T1.PersonId,
T3.Tel_no,
rank() over (
partition by T1.PersonId
order by T2.ID desc
) as rk
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
)
select
PersonId,
Tel_no
from TPRanked
where rk = 1

-- SQL Server 2000 or 7.0
select
T1.PersonId,
T3.Tel_no
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
where not exists (
select *
from #table1 as T1a left outer join #table2 as T2a
on T2a.PersonId = T1a.PersonId
left outer join #table3 as T3a
on T3a.ID = T2a.ID
and T3a.Type = 'Home'
where T1a.PersonId = T1.PersonId
and T2a.ID > T2.ID
)

Steve Kass
Drew University
http://www.stevekass.com
|||I have just tried the above query in SQL but the problem is that it will show a NULL value if a mobile no has a higher ID than a Home no.|||

I need to do the query without creating any temporary tables.

|||

Ok.. here it is..

Note: If you use temp table then you can increase the performance

Code Snippet

Create Table #table1 (

[PersonId] Int

);

Insert Into #table1 Values('111');

Insert Into #table1 Values('112');

Insert Into #table1 Values('113');

Insert Into #table1 Values('114');

Insert Into #table1 Values('207');

Create Table #table2 (

[PersonId] int ,

[ID] int

);

Insert Into #table2 Values('111','0122');

Insert Into #table2 Values('111','0123');

Insert Into #table2 Values('112','0124');

Insert Into #table2 Values('113','0125');

Insert Into #table2 Values('207','0126');

Insert Into #table2 Values('207','0127');

Create Table #table3 (

[ID] int ,

[Tel_no] Varchar(100) ,

[Type] Varchar(100)

);

Insert Into #table3 Values('0122','0125 23223','Hone');

Insert Into #table3 Values('0123','0122 43533','Home');

Insert Into #table3 Values('0124','0122 444111','Mobile');

Insert Into #table3 Values('0125','0777 47474','Mobile');

Insert Into #table3 Values('0126','01222 747474','Home');

Insert Into #table3 Values('0127','01232 484848','Home');

Select

A.PersonId

,B.Tel_No as Home_No

from

(Select PersonId, Max(Case When Type='Home' Then Id Else NULL END) ID

From (Select

X.PersonId

,Tel_No

,Y.ID

,Type

From

#Table1 X

Left Outer Join #Table2 Y On X.Personid=Y.PersonID

Left Outer Join #Table3 Z on Z.Id=Y.ID) as D Group By PersonId) A

Left Outer Join (Select

X.PersonId

,Tel_No

,Y.ID

,Type

From

#Table1 X

Left Outer Join #Table2 Y On X.Personid=Y.PersonID

Left Outer Join #Table3 Z on Z.Id=Y.ID) B On A.PersonId=B.PersonId And A.id=B.ID

|||I think I have a corrected query for SQL Server 2005. I changed the ranking criteria so that 'Home' numbers are always ranked highest. I'm working on the 2000 query. Obviously this needs careful testing!

with TPRanked as (
select
T1.PersonId, T2.ID,
T3.Tel_no,
rank() over (
partition by T1.PersonId
order by
case when T3.Type = 'Home' then 0 else 1 end,
T2.ID desc
) as rk
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
)
select
PersonId,
Tel_no
from TPRanked
where rk = 1

SK
|||Here's a correction for SQL 2000 along with another query that takes a different approach. The different approach doesn't generalize very well, but it's much more concise.

-- Quick and dirty
select
T1.PersonId,
(
select top (1) Tel_no
from #table2 as T2
join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'
where T2.PersonId = T1.PersonId
order by T3.ID desc
) as Tel_no
from #table1 as T1

-- Second try, first approach
select
T1.PersonId,
T3.Tel_no
from #table1 as T1 left outer join #table2 as T2
on T2.PersonId = T1.PersonId
left outer join #table3 as T3
on T3.ID = T2.ID
and T3.Type = 'Home'

where not exists (
select *
from #table1 as T1a left outer join #table2 as T2a
on T2a.PersonId = T1a.PersonId
left outer join #table3 as T3a
on T3a.ID = T2a.ID
and T3a.Type = 'Home'
where T1a.PersonId = T1.PersonId
and (
(T3a.Type = 'Home' and (T3.Type is null or T2a.ID > T2.ID)) or
(T3.Type is null and T2a.ID > T2.ID)
)
)

SK