November 27, 2007 at 8:57 am
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.
November 27, 2007 at 1:17 pm
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
November 27, 2007 at 1:57 pm
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
November 27, 2007 at 10:03 pm
Ummm.... I'm pretty sure that XML uses for tags and not { }... that may be part of the problem...
--Jeff Moden
Change is inevitable... Change for the better is not.
April 4, 2013 at 1:51 am
Nice
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply