January 14, 2017 at 8:07 am
Hi Experts ,
I have 2 table XML_main(which is having table columns) and XML_sub (data_values) , where columns are mention as 1 from table xml_main , those column values should added as a xml value in Xml_sub table. please find the input and output below. (conditions : null values should not be a part of xml consolidation values)
-- code :
create table XML_main(InstanceId int , FieldName varchar(100), IsXML bit )
insert into XML_main (InstanceId , FieldName , IsXML)
select 1 ,'Add1' , 0 union all select 2, 'Add2', 1 union all select 3 ,'Name' ,0 UNION ALL select 4 ,'Marks' , 0 union all select 5, 'Add5' , 1
create table XML_Sub (id int , add1 varchar(100), add2 varchar(100) , name varchar(100) , marks int , add5 varchar(100) , consolidated_xml xml)
insert into XML_Sub
select 1 , 'Add1' , 'add2' , 'sree' , 75 , 'add5' ,'<Add3>asd</Add3>' union all select 2 , 'Add1ab' , 'add2a' , 'shree' , 750 , 'add5s' ,'<Add3>asdbg</Add3>'
union all
select 3 ,'add1add',null,'Anand', 45, 'add5sq','<Add3>asd</Add3>'
-- input :
select * from XML_main
select * from XML_Sub
-- partial output (Expected output):
select 1 as id, 'Add1' as add1 , 'add2' as add2,'sree' as name, 75 as marks , 'add5' asadd5,cast('<Add3>asd</Add3><Add1>Add1</Add1><Add5>Add5</Add5>' as XML) as consolidated_xml
union all
select 2,'Add1ab','add2a','shree',750,'add5s','<Add3>asdbg</Add3><Add1>add1ab</Add1><Add5>Add5s</Add5>'
union all
select 3,NULL,'add1add','Anand',45,'add5sq','<Add3>asd</Add3><Add5>Add5sq</Add5>'
-- exact output (expected out put): (newly added columns are excluded)
select 1 as id, 'add2' as add2,'sree' as name, 75 as marks , cast('<Add3>asd</Add3><Add1>Add1</Add1><Add5>Add5</Add5>' as XML) as consolidated_xml
union all
select 2,'add2a','shree',750,'<Add3>asdbg</Add3><Add1>add1ab</Add1><Add5>Add5s</Add5>'
union all
select 3,NULL,'Anand',45,'<Add3>asd</Add3><Add5>Add5sq</Add5>'
January 14, 2017 at 1:49 pm
It might just be me, but I;m afraid I don't understand what you're trying to achieve here.
My confusion arises from "those column values should added as a xml value in Xml_sub table". I thought you meant that columns were the value of IsXML is 1 are meant to be in the XML column, however, in your example id 1 and 2 both have an XML value for Add1, however, Add1 how an IsXML value of 0.
Could you give a more comprehensive explanation of your logic please?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 15, 2017 at 11:28 pm
Hi Please some one help me who have a good knowledge in XML its little emergency.
January 16, 2017 at 1:58 am
Anandkumar-SQL_Developer - Sunday, January 15, 2017 11:28 PMHi Please some one help me who have a good knowledge in XML its little emergency.
Please see my post, I need a better explanation of your goals.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 16, 2017 at 4:22 am
Thom A - Monday, January 16, 2017 1:58 AMAnandkumar-SQL_Developer - Sunday, January 15, 2017 11:28 PMHi Please some one help me who have a good knowledge in XML its little emergency.Please see my post, I need a better explanation of your goals.
Hi brother ,
Really i don't understand , please make me little clearly understand, what is the relation between my question and your reply. i want code for that question . i dont have that much knowledge in XML.
January 16, 2017 at 4:34 am
Your question doesn't explain your logic. As i asked, I need to know why your output is what it is. To quote my previous post:
My confusion arises from "those column values should added as a xml value in Xml_sub table". I thought you meant that columns were the value of IsXML is 1 are meant to be in the XML column, however, in your example id 1 and 2 both have an XML value for Add1, however, Add1 how an IsXML value of 0.
Why is Add1 in your output? Is doesn't have an IsXMl of 1.
I don't know, or understand your data, you need to explain it, and give information as to how it works and why it ends up looking like does. Otherwise we have no idea how to even start when all you've given if 3 sets of data, containing XML, with no details of how you get from A to B to C.
P.s. I don't mean to seem rude, but my name is Thom, not Brother.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply