December 7, 2010 at 12:08 am
I have an xml explicit sql in which I want to add another xml childnode in one of the nodes but when i try a xml explicit query inside the bigger xml it encodes it and the xml is lost .
select Tag ,
Parent ,
[Question!1!] ,
[Answers!2!] ,
[Answer!3!] ,
[ItemID!4!] ,
[AnswerID!5!] ,
[AnswerWeightAge!6!] ,
[AnswerTitle!7!] ,
[AnswerDescription!8!] ,
[AnswerMarked!9!] ,
[CorrectAnswersT!10!]
from
(
select 1 as Tag ,
null as Parent ,
0 as AnswerID ,
null as 'Question!1!' ,
null as 'Answers!2!' ,
null as 'Answer!3!' ,
null as 'ItemID!4!' ,
null as 'AnswerID!5!' ,
null as 'AnswerWeightAge!6!' ,
null as 'AnswerTitle!7!' ,
null as 'AnswerDescription!8!' ,
null as 'AnswerMarked!9!' ,
null as 'CorrectAnswersT!10!'
union
select 2 as Tag ,
1 as Parent ,
0 as AnswerID ,
null as 'Question!1!' ,
null as 'Answers!2!' ,
null as 'Answer!3!' ,
null as 'ItemID!4!' ,
null as 'AnswerID!5!' ,
null as 'AnswerWeightAge!6!',
null as 'AnswerTitle!7!' ,
null as 'AnswerDescription!8!',
null as 'AnswerMarked!9!',
null as 'CorrectAnswersT!10!'
union
select 3 as Tag ,
2 as Parent ,
a.Answer_Id as AnswerID ,
null as 'Question!1!' ,
null as 'Answers!2!' ,
null as 'Answer!3!' ,
null as 'ItemID!4!' ,
null as 'AnswerID!5!' ,
null as 'AnswerWeightAge!6!',
null as 'AnswerTitle!7!' ,
null as 'AnswerDescription!8!',
null as 'AnswerMarked!9!',
null as 'CorrectAnswersT!10!'
from Item_Details id
join Answer a
on a.Answer_Id = id.Answer_Id
where id.Item_Id = 45
union
select 4 as Tag ,
3 as Parent ,
a.Answer_Id as AnswerID ,
null as 'Question!1!' ,
null as 'Answers!2!' ,
null as 'Answer!3!' ,
id.Item_Id as 'ItemID!4!' ,
null as 'AnswerID!5!' ,
null as 'AnswerWeightAge!6!',
null as 'AnswerTitle!7!' ,
null as 'AnswerDescription!8!',
null as 'AnswerMarked!9!',
null as 'CorrectAnswersT!10!'
from Item_Details id
join Answer a
on a.Answer_Id = id.Answer_Id
where id.Item_Id = 45
union
select 5 as Tag ,
3 as Parent ,
a.Answer_Id as AnswerID ,
null as 'Question!1!' ,
null as 'Answers!2!' ,
null as 'Answer!3!' ,
null as 'ItemID!4!' ,
a.Answer_Id as 'AnswerID!5!' ,
null as 'AnswerWeightAge!6!' ,
null as 'AnswerTitle!7!' ,
null as 'AnswerDescription!8!',
null as 'AnswerMarked!9!',
null as 'CorrectAnswersT!10!'
from Item_Details id
join Answer a
on a.Answer_Id = id.Answer_Id
where id.Item_Id = 45
union
select 6 as Tag ,
3 as Parent ,
a.Answer_Id as AnswerID ,
null as 'Question!1!' ,
null as 'Answers!2!' ,
null as 'Answer!3!' ,
null as 'ItemID!4!' ,
null as 'AnswerID!5!' ,
cast(a.Answer_Weightage as decimal(9,2)) as 'AnswerWeightAge!6!' ,
null as 'AnswerTitle!7!' ,
null as 'AnswerDescription!8!',
null as 'AnswerMarked!9!',
null as 'CorrectAnswersT!10!'
from Item_Details id
join Answer a
on a.Answer_Id = id.Answer_Id
where id.Item_Id = 45
union
select 7 as Tag ,
3 as Parent ,
a.Answer_Id as AnswerID ,
null as 'Question!1!' ,
null as 'Answers!2!' ,
null as 'Answer!3!' ,
null as 'ItemID!4!' ,
null as 'AnswerID!5!' ,
null as 'AnswerWeightAge!6!' ,
a.Answer_Title as 'AnswerTitle!7!' ,
null as 'AnswerDescription!8!',
null as 'AnswerMarked!9!',
null as 'CorrectAnswersT!10!'
from Item_Details id
join Answer a
on a.Answer_Id = id.Answer_Id
where id.Item_Id = 45
union
select 8 as Tag ,
3 as Parent ,
a.Answer_Id as AnswerID ,
null as 'Question!1!' ,
null as 'Answers!2!' ,
null as 'Answer!3!' ,
null as 'ItemID!4!' ,
null as 'AnswerID!5!' ,
null as 'AnswerWeightAge!6!' ,
null as 'AnswerTitle!7!' ,
a.Answer_Description as 'AnswerDescription!8!',
null as 'AnswerMarked!9!',
null as 'CorrectAnswersT!10!'
from Item_Details id
join Answer a
on a.Answer_Id = id.Answer_Id
where id.Item_Id = 45
union
select 9 as Tag ,
3 as Parent ,
a.Answer_Id as AnswerID ,
null as 'Question!1!' ,
null as 'Answers!2!' ,
null as 'Answer!3!' ,
null as 'ItemID!4!' ,
null as 'AnswerID!5!' ,
null as 'AnswerWeightAge!6!' ,
null as 'AnswerTitle!7!' ,
null as 'AnswerDescription!8!',
--case when rr.Answer_Response = a.Answer_Id then 1 else 0 end as 'AnswerMarked!9!'
null as 'AnswerMarked!9!',
null as 'CorrectAnswersT!10!'
from Item_Details id
join Answer a
on a.Answer_Id = id.Answer_Id
where id.Item_Id = 45
union
select 10 as Tag ,
3 as Parent ,
a.Answer_Id as AnswerID ,
null as 'Question!1!' ,
null as 'Answers!2!' ,
null as 'Answer!3!' ,
null as 'ItemID!4!' ,
null as 'AnswerID!5!' ,
null as 'AnswerWeightAge!6!' ,
null as 'AnswerTitle!7!' ,
null as 'AnswerDescription!8!',
--case when rr.Answer_Response = a.Answer_Id then 1 else 0 end as 'AnswerMarked!9!'
null as 'AnswerMarked!9!',
(
select Tag ,
Parent ,
[CorrectAnswers!1!],
[CorrectAnswer!2!] ,
[AnswerID!3!] ,
[AnswerWeightAge!4!] ,
[AnswerTitle!5!] ,
[AnswerDescription!6!]
from
(
select 1 as Tag ,
null as Parent ,
0 as AnswerID ,
null as 'CorrectAnswers!1!' ,
null as 'CorrectAnswer!2!' ,
null as 'AnswerID!3!' ,
null as 'AnswerWeightAge!4!' ,
null as 'AnswerTitle!5!' ,
null as 'AnswerDescription!6!'
union
select 2 as Tag ,
1 as Parent ,
a.Answer_Id as AnswerID ,
null as 'CorrectAnswers!1!' ,
null as 'CorrectAnswer!2!' ,
null as 'AnswerID!3!' ,
null as 'AnswerWeightAge!4!' ,
null as 'AnswerTitle!5!' ,
null as 'AnswerDescription!6!'
from Item_Details id
join Answer a
on id.Answer_Id = a.Answer_Id
where id.Item_Id = 45
and a.Answer_Weightage = (select MAX(Answer_Weightage) from Item_Details id
join Answer a
on id.Answer_Id = a.Answer_Id
where id.Item_Id = 45)
union
select 3 as Tag ,
2 as Parent ,
a.Answer_Id as AnswerID ,
null as 'CorrectAnswers!1!' ,
null as 'CorrectAnswer!2!' ,
a.Answer_Id as 'AnswerID!3!' ,
null as 'AnswerWeightAge!4!' ,
null as 'AnswerTitle!5!' ,
null as 'AnswerDescription!6!'
from Item_Details id
join Answer a
on id.Answer_Id = a.Answer_Id
where id.Item_Id = 45
and a.Answer_Weightage = (select MAX(Answer_Weightage) from Item_Details id
join Answer a
on id.Answer_Id = a.Answer_Id
where id.Item_Id = 45)
union
select 4 as Tag ,
2 as Parent ,
a.Answer_Id as AnswerID ,
null as 'CorrectAnswers!1!' ,
null as 'CorrectAnswer!2!' ,
null as 'AnswerID!3!' ,
a.Answer_Weightage as 'AnswerWeightAge!4!' ,
null as 'AnswerTitle!5!' ,
null as 'AnswerDescription!6!'
from Item_Details id
join Answer a
on id.Answer_Id = a.Answer_Id
where id.Item_Id = 45
and a.Answer_Weightage = (select MAX(Answer_Weightage) from Item_Details id
join Answer a
on id.Answer_Id = a.Answer_Id
where id.Item_Id = 45)
union
select 5 as Tag ,
2 as Parent ,
a.Answer_Id as AnswerID ,
null as 'CorrectAnswers!1!' ,
null as 'CorrectAnswer!2!' ,
null as 'AnswerID!3!' ,
null as 'AnswerWeightAge!4!' ,
a.Answer_Title as 'AnswerTitle!5!' ,
null as 'AnswerDescription!6!'
from Item_Details id
join Answer a
on id.Answer_Id = a.Answer_Id
where id.Item_Id = 45
and a.Answer_Weightage = (select MAX(Answer_Weightage) from Item_Details id
join Answer a
on id.Answer_Id = a.Answer_Id
where id.Item_Id = 45)
union
select 6 as Tag ,
2 as Parent ,
a.Answer_Id as AnswerID ,
null as 'CorrectAnswers!1!' ,
null as 'CorrectAnswer!2!' ,
null as 'AnswerID!3!' ,
null as 'AnswerWeightAge!4!' ,
null as 'AnswerTitle!5!' ,
a.Answer_Description as 'AnswerDescription!6!'
from Item_Details id
join Answer a
on id.Answer_Id = a.Answer_Id
where id.Item_Id = 45
and a.Answer_Weightage = (select MAX(Answer_Weightage) from Item_Details id
join Answer a
on id.Answer_Id = a.Answer_Id
where id.Item_Id = 45)
) A
order by AnswerID
for xml explicit
) as 'CorrectAnswersT!10!'
from Item_Details id
join Answer a
on a.Answer_Id = id.Answer_Id
where id.Item_Id = 45
) A
order by AnswerID
for xml explicit
results in
<Question>
<Answers>
<Answer>
<ItemID>45</ItemID>
<AnswerID>68</AnswerID>
<AnswerWeightAge>1.00</AnswerWeightAge>
<AnswerTitle>Option1</AnswerTitle>
<AnswerDescription>Option1</AnswerDescription>
<AnswerMarked />
<CorrectAnswersT><CorrectAnswers><CorrectAnswer><AnswerID>68</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option1</AnswerTitle><AnswerDescription>Option1</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>69</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option2</AnswerTitle><AnswerDescription>Option2</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>70</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option3</AnswerTitle><AnswerDescription>Option3</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>71</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option4</AnswerTitle><AnswerDescription>Option4</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>72</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option5</AnswerTitle><AnswerDescription>Option5</AnswerDescription></CorrectAnswer></CorrectAnswers></CorrectAnswersT>
</Answer>
<Answer>
<ItemID>45</ItemID>
<AnswerID>69</AnswerID>
<AnswerWeightAge>1.00</AnswerWeightAge>
<AnswerTitle>Option2</AnswerTitle>
<AnswerDescription>Option2</AnswerDescription>
<AnswerMarked />
<CorrectAnswersT><CorrectAnswers><CorrectAnswer><AnswerID>68</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option1</AnswerTitle><AnswerDescription>Option1</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>69</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option2</AnswerTitle><AnswerDescription>Option2</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>70</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option3</AnswerTitle><AnswerDescription>Option3</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>71</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option4</AnswerTitle><AnswerDescription>Option4</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>72</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option5</AnswerTitle><AnswerDescription>Option5</AnswerDescription></CorrectAnswer></CorrectAnswers></CorrectAnswersT>
</Answer>
<Answer>
<ItemID>45</ItemID>
<AnswerID>70</AnswerID>
<AnswerWeightAge>1.00</AnswerWeightAge>
<AnswerTitle>Option3</AnswerTitle>
<AnswerDescription>Option3</AnswerDescription>
<AnswerMarked />
<CorrectAnswersT><CorrectAnswers><CorrectAnswer><AnswerID>68</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option1</AnswerTitle><AnswerDescription>Option1</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>69</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option2</AnswerTitle><AnswerDescription>Option2</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>70</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option3</AnswerTitle><AnswerDescription>Option3</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>71</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option4</AnswerTitle><AnswerDescription>Option4</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>72</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option5</AnswerTitle><AnswerDescription>Option5</AnswerDescription></CorrectAnswer></CorrectAnswers></CorrectAnswersT>
</Answer>
<Answer>
<ItemID>45</ItemID>
<AnswerID>71</AnswerID>
<AnswerWeightAge>1.00</AnswerWeightAge>
<AnswerTitle>Option4</AnswerTitle>
<AnswerDescription>Option4</AnswerDescription>
<AnswerMarked />
<CorrectAnswersT><CorrectAnswers><CorrectAnswer><AnswerID>68</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option1</AnswerTitle><AnswerDescription>Option1</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>69</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option2</AnswerTitle><AnswerDescription>Option2</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>70</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option3</AnswerTitle><AnswerDescription>Option3</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>71</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option4</AnswerTitle><AnswerDescription>Option4</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>72</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option5</AnswerTitle><AnswerDescription>Option5</AnswerDescription></CorrectAnswer></CorrectAnswers></CorrectAnswersT>
</Answer>
<Answer>
<ItemID>45</ItemID>
<AnswerID>72</AnswerID>
<AnswerWeightAge>1.00</AnswerWeightAge>
<AnswerTitle>Option5</AnswerTitle>
<AnswerDescription>Option5</AnswerDescription>
<AnswerMarked />
<CorrectAnswersT><CorrectAnswers><CorrectAnswer><AnswerID>68</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option1</AnswerTitle><AnswerDescription>Option1</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>69</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option2</AnswerTitle><AnswerDescription>Option2</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>70</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option3</AnswerTitle><AnswerDescription>Option3</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>71</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option4</AnswerTitle><AnswerDescription>Option4</AnswerDescription></CorrectAnswer><CorrectAnswer><AnswerID>72</AnswerID><AnswerWeightAge>1.000000000000000e+000</AnswerWeightAge><AnswerTitle>Option5</AnswerTitle><AnswerDescription>Option5</AnswerDescription></CorrectAnswer></CorrectAnswers></CorrectAnswersT>
</Answer>
</Answers>
</Question>
Even though they are being shown as XML tags cause of what i believe is html encoding but
after XML tag <CorrectAnswersT> all "<" are being encodes as "&" + "lt;" I hope everyone gets its 🙂
I want an xml child node not encode tags which are being interpreted as text , please help .
December 7, 2010 at 2:56 am
The FOR XML clause was introduced in SQL Server 2000. At that time SQL Server didn’t have the XML data type, so it returned a regular string. XML data type was introduced with SQL Server 2005, but the behavior of FOR XML clause was kept for backwards compatibility. In most cases we don’t care that it returns a string instead of XML data type, but when we try to add the results of a query with FOR XML clause to another XML it does matter (just as you found out). In order to add and retain the XML tags, you have to use the key word type that was introduced with SQL Server 2005 and directs the server that the result of the query with the FOR XML clause should be XML and not string. See my example bellow:
use tempdb
go
if exists (select * from sys.objects where name = 'Customers')
drop table Customers
go
--Creating customers table
create table Customers (CustomerID varchar(15) not null primary key,
ContactName varchar(20),
CompanyName varchar(40))
go
IF EXISTS (select * from sys.objects where name = 'Orders')
drop table Orders
go
--Creating Orders table
create table Orders (OrderID int not null primary key,
CustomerID varchar(15) not null,
OrderDate datetime not null)
go
--Inserting customers
insert into Customers (CustomerID, ContactName, CompanyName)
select 'LEHMS','Renate Messner','Lehmanns Marktstand'
union select 'MAGAA','Giovanni Rovelli','Magazzini Alimentari Riuniti'
--Inserting Orders
Insert into Orders (OrderID, CustomerID, OrderDate)
select 10343,'LEHMS','Oct 31 1996 12:00AM'
union select 10404,'MAGAA','Jan 3 1997 12:00AM'
union select 10467,'MAGAA','Mar 6 1997 12:00AM'
union select 10497,'LEHMS','Apr 4 1997 12:00AM'
union select 10522,'LEHMS','Apr 30 1997 12:00AM'
union select 10534,'LEHMS','May 12 1997 12:00AM'
go
--Notice the key word type. Run this code
--with it and without it and notice the difference
select CustomerID as 'CustomerDet/@CustomerID',
ContactName as 'CustomerDet/ContactID',
CompanyName as 'CustomerDet/CompanyName',
(select OrderID as 'Order/@OrderID', OrderDate as 'Order/@OrderDate'
from Orders where Orders.CustomerID = Customers.CustomerID
for xml path(''), type) as 'CustomerDet/OrdersList'
from Customers
for xml path(''), root('Orders')
go
--cleanup
drop table Customers
drop table Orders
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 7, 2010 at 3:31 am
Thanks I tired it with the type keyword and tried to cast the string as xml type
But the query gave an error as
"The xml data type cannot be selected as DISTINCT because it is not comparable."
Any Idea's
December 8, 2010 at 10:25 pm
nikshepmehra (12/7/2010)
Thanks I tired it with the type keyword and tried to cast the string as xml typeBut the query gave an error as
"The xml data type cannot be selected as DISTINCT because it is not comparable."
Any Idea's
What version of SQL Server are you using?
December 9, 2010 at 1:48 am
SQL Server 2008 R2
December 9, 2010 at 11:35 am
Ah, that may be where the disconnect is here. You posted your thread in the 2005 forum. There is a seperate forum for 2008 questions.
With that said, I don't have a 2008 instance to test Adi's example in, but it works fine for me in 2005. You may need to play around with it. I'd encourage you to read through the FOR XML topics in BOL. The way you are structuring your XML will be a mainenance nightmare for you. You want to take advantage of the XML parser and the improved XML handing that SQL Server 2005 / 2008 offer instead of attempting to build it all out manually like that.
Post your table DDL and a few rows of sample data and we can get you moving in the right direction.
December 9, 2010 at 2:20 pm
nikshepmehra (12/7/2010)
Thanks I tired it with the type keyword and tried to cast the string as xml typeBut the query gave an error as
"The xml data type cannot be selected as DISTINCT because it is not comparable."
Any Idea's
You have to do the DISTINCT in a subquery or CTE and then the XML in the parent query (which may or may not be the main query).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply