March 18, 2011 at 9:29 am
Hey,
actually I have the problem, that I want to add some xml childnodes (existing in a table as xml-datatype) under specific xml data. In the following example you can see this problem:
This is the target xml data:
<root root_id="1">
<subroot subroot_id="1">
<data value="test"/>
<data value="test2"/>
<otherdata value="test3"/>
</subroot>
</root>
I have the table root with foreign key to table subroot and under subroot I want to add the generated xml-rows as text, but not with taking a cursor.
If I take the table with the xml-data I have an subnode with the name of the table, and I only want to insert the rows between the right subroot.
//EDIT: I have added the DDL for the tables - I hope the syntax is correct without checking this on sql server, because its weekend 🙂
create table root(
root_id int primary key clustered
)
GO
create table subroot(
subroot_id int primary key clustered,
root_id int,
Foreign Key (root_id) references root(root_id)
)
GO
create table tempxml(
subroot_id int,
childnodes xml,
Foreign Key (subroot_id) references subroot(subroot_id)
)
GO
With the following statement I create the xml-Data
select root.root_id,
subroot.subroot_id,
tempxml.childnodes
from root
inner join
subroot on root.root_id=subroot.root_id
inner join
tempxml on tempxml.subroot_id=subroot_id.subroot_id
for xml auto
and the result is:
<root root_id="1">
<subroot subroot_id="1">
<tempxml>
<data value="test"/>
<data value="test2"/>
<otherdata value="test3"/>
</tempxml>
</subroot>
</root>
How can I solve the problem?
Thanks.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
March 18, 2011 at 11:15 am
I would like to help...please post the DDL for your tables and some DML to create some sample data. Have a look at this article if you have doubts about my request: www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 18, 2011 at 3:03 pm
Hi opc.three,
sorry for my writing form ... it was friday afternoon before going home ... 🙂 ... the next time I will obey the rules. I have updated my initial post ...
Thanks.
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
March 18, 2011 at 4:57 pm
I made up some DML since you did not provide any :Whistling:
INSERT INTO dbo.root(root_id)VALUES (1)
INSERT INTO dbo.subroot(subroot_id, root_id)VALUES (1,1)
INSERT INTO dbo.tempxml(subroot_id, childnodes)VALUES (1,'<data value="test"/><data value="test2"/><otherdata value="test3"/>')
With your DDL and my DML and query I matched your desired resultset. If it's not perfect because I guessed wrong on the DML you'll at least get the idea and should be able to get to a workable solution.
SELECT [root].root_id AS [root_id],
subroot.subroot_id AS [subroot_id],
(SELECT childnodes
FROM dbo.tempxml
WHERE subroot_id = subroot.subroot_id
)
FROM [dbo].[root] AS [root]
INNER JOIN dbo.subroot AS subroot ON [root].root_id = subroot.root_id
FOR XML AUTO,
ROOT('root')
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 21, 2011 at 4:35 am
Hi opc.three,
it works for me. Thanks for providing me the right solution.
I think it was too simple to get it on friday afternoon ... 😎
Greets
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply