Converting xml format to another xml format using xml

Question

Converting xml format to another xml format using xml

kunalharia84

SSC Rookie

Points: 37
More actions
October 2, 2009 at 12:54 pm

#205290

I have a column in a table which stores some xml information in a format. Now i want to transfer this information to another table in a new format. I tried doing this populating a temp table with the edge table (using openxml) information and getting the required nodes. I am thinking if somebody has done something similar and can help me out. The format of the old and new xml is as follows
Old format
"<Clause Tag="4" IsLeaf="0" Operator="OR">
<Clause Tag="2" IsLeaf="0" Operator="=">
<Clause Tag="0" IsLeaf="-1" Operator="">
Fruit
</Clause>
<Clause Tag="1" IsLeaf="-1" Operator="">
Apple
</Clause>
</Clause>
<Clause Tag="2" IsLeaf="0" Operator="=">
<Clause Tag="0" IsLeaf="-1" Operator="">
Fruit
</Clause>
<Clause Tag="1" IsLeaf="-1" Operator="">
Banana
</Clause>
</Clause>
</Clause>
"
New format is
"<AdditionalConditions>
<ComplexCondition operator="OR">
<LeafCondition leftOperand="Fruit" leftOperandType="Field" operator="Equal" rightOperand="'Apple" >
<LeafCondition leftOperand="Fruit" leftOperandType="Field" operator="Equal" rightOperand="'Banana" >
</ComplexCondition>
</AdditionalConditions>
"

Viewing 8 posts - 1 through 8 (of 8 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'd probably have to use XQuery to turn it into a relational format, then use For XML to turn it into the new format. There may be a more efficient way, but that's how I'd do it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kunalharia84 SSC Rookie Points: 37 More actions · Answer 2

I am not familiar with xquery, can you provide an example?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

This should get you started (on the shredding out part). depending on how many levels you have, this could get rather ugly.

declare @x xml

set @x='<root><Clause Tag="4" IsLeaf="0" Operator="OR">

<Clause Tag="2" IsLeaf="0" Operator="=">

<Clause Tag="0" IsLeaf="-1" Operator="">

Fruit

</Clause>

<Clause Tag="1" IsLeaf="-1" Operator="">

Apple

</Clause>

<Clause Tag="2" IsLeaf="0" Operator="=">

<Clause Tag="0" IsLeaf="-1" Operator="">

Fruit

</Clause>

<Clause Tag="1" IsLeaf="-1" Operator="">

Banana

</Clause>

</Clause></root>'

select

nodetest.value('../@Tag','int') prevtag,

nodetest.value('../@Operator','varchar(20)') prevop ,

nodetest.value('./@Tag','int') tag,

nodetest.value('./@IsLeaf','int') IsLeaf,

nodetest.value('(Clause[1]/.)[1]','varchar(50)') valleft ,

nodetest.value('./@Operator','varchar(20)') op ,

nodetest.value('(Clause[2]/.)[1]','varchar(50)') valRight

from @x.nodes('/root/Clause/Clause') as x2(nodetest)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Here's a bit more expansive an example (where there is more than one crit)

declare @x xml

set @x='

<root>

<Clause Tag="4" IsLeaf="0" Operator="OR">

<Clause Tag="2" IsLeaf="0" Operator="=">

<Clause Tag="0" IsLeaf="-1" Operator="">Fruit</Clause>

<Clause Tag="1" IsLeaf="-1" Operator="">Apple</Clause>

</Clause>

<Clause Tag="2" IsLeaf="0" Operator="=">

<Clause Tag="0" IsLeaf="-1" Operator="">Fruit</Clause>

<Clause Tag="1" IsLeaf="-1" Operator="">Banana</Clause>

</Clause>

<Clause Tag="4" IsLeaf="0" Operator="AND">

<Clause Tag="2" IsLeaf="0" Operator="NEQ">

<Clause Tag="0" IsLeaf="-1" Operator="">NAME</Clause>

<Clause Tag="1" IsLeaf="-1" Operator="">Bob</Clause>

</Clause>

<Clause Tag="2" IsLeaf="0" Operator="NEQ">

<Clause Tag="0" IsLeaf="-1" Operator="">NAME</Clause>

<Clause Tag="1" IsLeaf="-1" Operator="">Joe</Clause>

</Clause>

</root>'

;with n as

(

select row_number() over (order by nodetest) rn,

cast(nodetest.query('.') as XML) nd,

nodetest.value('./@Tag','int') tag1,

nodetest.value('./@Operator','varchar(20)') op1

from @x.nodes('/root/Clause') as x2(nodetest)

),

n2 as

(

select

n.rn,

n.nd,

t.value('./@Tag','int') tag2,

t.value('./@Operator','varchar(20)') op2,

t.value('./@IsLeaf','int') IsLeaf,

t.value('(Clause[1]/.)[1]','varchar(50)') valleft ,

t.value('./@Operator','varchar(20)') op ,

t.value('(Clause[2]/.)[1]','varchar(50)') valRight,

cast(t.query('.') as XML) nd2

from n

cross apply n.nd.nodes('/Clause/Clause') nd2(t)

)

select * from n2

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

kunalharia84 SSC Rookie Points: 37 More actions · Answer 5

Thanks Matt for the replies. I will try this later today. The problem with getting the edge table values is the inconsistent format of the original xml. I tried this as i was familiar with the openxml function of sql server.

WIll let you know how his goes...

kunalharia84 SSC Rookie Points: 37 More actions · Answer 6

I tried the solution that you provided. It works great except when i encounter this source xml. The problrm lies during parsing of valleft/operator for "fruit = Apple" and "fruit = grapes " part. It concatenates the right and left operands. If you use this xml and run your query, you will come to know. I tries changing the values in the query all weekend but with my limited knowledge of xml, i could not get through.

"<root><Clause Tag="3" IsLeaf="0" Operator="AND">

Fruit

</Clause>

Banana

</Clause>

Fruit

</Clause>

Apple

</Clause>

Fruit

</Clause>

Grapes

</Clause>

Fruit

</Clause>

berries

</Clause>

</Clause> </root>"

kunalharia84 SSC Rookie Points: 37 More actions · Answer 7

Hey Matt, I got a work around for this and figured out how to use this.

Thanks for the reply.

-Kunal