Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML Data Extract Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2007 8:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 7:58 AM
Points: 69, Visits: 54
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.



Post #426435
Posted Tuesday, November 27, 2007 1:17 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:20 AM
Points: 1,853, Visits: 1,152
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


Post #426577
Posted Tuesday, November 27, 2007 1:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 30, 2013 7:58 AM
Points: 69, Visits: 54
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



Post #426585
Posted Tuesday, November 27, 2007 10:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:37 AM
Points: 36,753, Visits: 31,210
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #426694
Posted Thursday, April 4, 2013 1:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 15, 2014 2:04 AM
Points: 1, Visits: 20
Nice
Post #1438670
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse