add xmlnodes to "for xml auto"-statement

Question

Post reply

add xmlnodes to "for xml auto"-statement

Patrick_Fiedler

SSCommitted

Points: 1874
More actions
March 18, 2011 at 9:29 am

#99561

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
Fiedler SQL Consulting
www.fiedler-sql-consulting.de
[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

Orlando Colamatteo

SSC Guru

Points: 182276

March 18, 2011 at 11:15 am

#1300682

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: qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Patrick_Fiedler SSCommitted Points: 1874 More actions · Answer 2

Patrick_Fiedler

SSCommitted

Points: 1874

March 18, 2011 at 3:03 pm

#1300818

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

Fiedler SQL Consulting

www.fiedler-sql-consulting.de
[/font]

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

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