Shred XML

Question

Shred XML

fafful

SSC Eights!

Points: 810
More actions
May 3, 2017 at 6:01 am

#336715

Thanks. I am trying to shred the xml below but getting more rows instead of 3 rows. Any help?
I want to get:
Ref    | loc    | gcode | newcode| CycleQTY |CycleCost    |
242    | R1    | X00331| GCOOPER|3   |    5.00    |
242    | R2    | X00332|GACOOPER| 6   |    9.00    |
242    | R1    | Null    | GGOIL | Null |    Null    |
My SQL:
declare @myXml xml;
@MyXml=N'<MyXML>
<NewComponents>
<TopRef locpoint="BSAMMUSA" >242
    <Comps ref="242" name="Joe">
    <Comp gcode="X00331" loc="R1" newcode="GCOOPER">
    <CAttr />
    <CCycles>
    <Cycle QTY="3" Cost="5.00" />
    </CCycles>
    </Comp>
    <Comp gcode="X00332" loc="R2" newcode="GACOOPER">
    <CAttr />
    <CCycles>
    <Cycle QTY="6" Cost="9.00" />
    </CCycles>
    </Comp>
    <Comp gcode="" loc="R1" newcode="GGOIL"/>
    </CCycles>
    </Comp>
    </Comps>
    </TopRef>
</NewComponents>
</MyXML>'
;
with Mains
(Ref,loc,gcode,newcode,CycleQTY,CycleCost)
As
(
select
f.c.value('TopRef[1]', 'varchar(50)') as ref
,g.c.value('@loc[1]', 'varchar(50)') as loc
,g.c.value('@gcode[1]', 'varchar(50)') as gcode
,g.c.value('@newcode[1]', 'varchar(10)') as newcode
,p.cy.value('@QTY[1]', 'varchar(10)') as CycleQTY
,p.cy.value('@Cost[1]','varchar(20)') as CycleCost
from @MyXml.nodes('/MyXML/NewComponents') as f(c)
cross apply @MyXml.nodes('/MyXML/NewComponents/TopRef/Comps/Comp') as g(c)
cross apply @MyXml.nodes('/MyXML/NewComponents/TopRef/Comps/Comp/CCycles/Cycle') as p(cy)
)
select * from Mains
;

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

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

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Change
cross apply @MyXml.nodes('/MyXML/NewComponents/TopRef/Comps/Comp/CCycles/Cycle') as p(cy)
to
cross apply g.c.nodes('CCycles/Cycle') as p(cy)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

Quick thought, no need for this complexity in the script, here is a simplified example
😎
declare @myXml xml=N'<MyXML> <NewComponents> <TopRef locpoint="BSAMMUSA" >242 <Comps ref="242" name="Joe"> <Comp gcode="X00331" loc="R1" newcode="GCOOPER"> <CAttr /> <CCycles> <Cycle QTY="3" Cost="5.00" /> </CCycles> </Comp> <Comp gcode="X00332" loc="R2" newcode="GACOOPER"> <CAttr /> <CCycles> <Cycle QTY="6" Cost="9.00" /> </CCycles> </Comp> <Comp gcode="X00333" loc="R2" newcode="GGOIL"> <CAttr /> <CCycles> </CCycles> </Comp> </Comps> </TopRef> </NewComponents> </MyXML>' ;

select g.c.value('(../../../TopRef/text())[1]', 'varchar(50)') as ref ,g.c.value('@loc[1]', 'varchar(50)') as loc ,g.c.value('@gcode[1]', 'varchar(50)') as gcode ,g.c.value('@newcode[1]', 'varchar(10)') as newcode ,g.c.value('CCycles[1]/Cycle[1]/@QTY[1]', 'varchar(10)') as CycleQTY ,g.c.value('CCycles[1]/Cycle[1]/@Cost[1]','varchar(20)') as CycleCost from @MyXml.nodes('/MyXML/NewComponents/TopRef/Comps/Comp') as g(c);

fafful SSC Eights! Points: 810 More actions · Answer 3

fafful

SSC Eights!

Points: 810

May 3, 2017 at 7:54 am

#1940890

Perfect. It works for me. Thanks

DesNorton SSC-Insane Points: 24386 More actions · Answer 4

Eirikur Eiriksson - Wednesday, May 3, 2017 7:48 AM
Quick thought, no need for this complexity in the script, here is a simplified example
😎
select g.c.value('(../../../TopRef/text())[1]', 'varchar(50)') as ref ,g.c.value('@loc[1]', 'varchar(50)') as loc ,g.c.value('@gcode[1]', 'varchar(50)') as gcode ,g.c.value('@newcode[1]', 'varchar(10)') as newcode ,g.c.value('CCycles[1]/Cycle[1]/@QTY[1]', 'varchar(10)') as CycleQTY ,g.c.value('CCycles[1]/Cycle[1]/@Cost[1]','varchar(20)') as CycleCost from @MyXml.nodes('/MyXML/NewComponents/TopRef/Comps/Comp') as g(c);

@Eirikur

According to this post on SO, the use of the parent axis (..) adds additional overhead as it causes the xml parser to reparse the entire xml
I have not had time to test it though.

How to post data/code on a forum to get the best help.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 5

DesNorton - Wednesday, May 3, 2017 8:27 AM
Eirikur Eiriksson - Wednesday, May 3, 2017 7:48 AM
Quick thought, no need for this complexity in the script, here is a simplified example
😎
select g.c.value('(../../../TopRef/text())[1]', 'varchar(50)') as ref ,g.c.value('@loc[1]', 'varchar(50)') as loc ,g.c.value('@gcode[1]', 'varchar(50)') as gcode ,g.c.value('@newcode[1]', 'varchar(10)') as newcode ,g.c.value('CCycles[1]/Cycle[1]/@QTY[1]', 'varchar(10)') as CycleQTY ,g.c.value('CCycles[1]/Cycle[1]/@Cost[1]','varchar(20)') as CycleCost from @MyXml.nodes('/MyXML/NewComponents/TopRef/Comps/Comp') as g(c);
@Eirikur
According to this post on SO, the use of the parent axis (..) adds additional overhead as it causes the xml parser to reparse the entire xml
I have not had time to test it though.

You are right ,it does add a little bit of overhead but that is much smaller than the additional application of the nodes method which implies additional XML construct/reconstruct etc. In this instance, one follows the cardinality of the desired output.
😎
Further, one has to use outer apply rather than cross apply to retrieve the empty/NULL values if cascading nodes.