Post reply

XML Data Extract

  • November 27, 2007 at 8:57 am

    #62887

    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

    #754721

    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

    #754726

    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

    #754816

    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.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 4, 2013 at 1:51 am

    #1603452

    Nice

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

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