SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Data Extract


XML Data Extract

Author
Message
reddyg
reddyg
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 89
I would appreciate if any one can help me extracting the data in to SQL server from the the following xml format.I have tried OPENXML using BOL examples but this xml format is little different.
If it is easy with SQLXMLBulkLoad that's also helpfull for me.

We are using SQL2000

Unfortunately I am having problem posting the xml data here.

So Replaced xml tags with { and }


{Item}
{SKU_Number} TEST SKU {/SKU_Number}
{Brand}XYZ-Brand{/Brand}
{Status}ACTIVE{/Status}
{GlobalAttributes}
{Attribute Name="Capacity" Group="Battery Features"}6000{/Attribute}
{Attribute Name="Chem" Group="Bat Features"}Lith{/Attribute}
{Attribute Name="EquiTO" Group="Bat Feature"}Sharp 3xrfgh{/Attribute}
{/GlobalAttributes}
{/Item}






Thanks
Reddy.



Joel Ewald
Joel Ewald
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2732 Visits: 1741
I do not think you can accomplish what you need using the WITH command, since you have multiple detail level lines using both element and attribute centric values.

I played with it a bit but could not get it to work correctly when specifying the attribute paths for each field.

You may have return the results as an edge table and extract your information.
You could save it to a temp table and build your records off of that.
If I get some time later I will see if I can find or write something that would read the edge table.

There may be some utility out there using a CTE to recursively and smartly build a row from an edge table but I did not see any with a quick search.


DECLARE @Xml VARCHAR(4000)
SET @Xml = YourXMLChunk

-- Initialize XML handle
DECLARE @hdoc INT
EXEC sp_xml_preparedocument @hdoc OUTPUT, @Xml

SELECT Id, ParentId, NodeType, LocalName, [Text]
FROM OPENXML ( @hdoc, '/', 2 ) AS x

-- Release XML handle
EXEC sp_xml_removedocument @hdoc


reddyg
reddyg
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 89
Joel,
Thanks for the details,I really appreciate your time and help.I have worked several of these XML import and export to SQL DB but not like this one.

Is this format of xml valid for data extract to SQL Db directly with OPENXML ??It seems little typical.

I will look farward for further help on extracting the edge table.

Thanks
Reddy



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85274 Visits: 41078
Ummm.... I'm pretty sure that XML uses for tags and not { }... that may be part of the problem...

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
neelait
neelait
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 28
Nice
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search