XML file data read through Stored Procedure

  • Hi,

    We have a table that is containing the XML file data(whole xml file data).

    We have a requirement to pull the Tag name and Tag Value from the table data and insert into the other table.

    The new table format like:

    ID Varchar(100)

    ComputerIDVarchar(100)

    FieldNameVarchar(100)

    FieldValueVarchar(100)

    I would be greateful if any one of you could please let me know how i can pull the xml tag and value details from the table data and insert into the other table by using the sql stored procedure?

    Please find the XML data file attached.

    Many Thanks,

    Madhu

  • Does this help?

    declare @XML XML;

    select @XML = ' ';

    select x.y.value('local-name(.)', 'VARCHAR(50)'),

    x.y.value('.', 'VARCHAR(50)')

    from @XML.nodes('//*') x(y);

    That's sample XML, but it pulls name-value pairs from it, and that sounds like what you need.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (3/27/2009)


    Does this help?

    declare @XML XML;

    select @XML = ' ';

    select x.y.value('local-name(.)', 'VARCHAR(50)'),

    x.y.value('.', 'VARCHAR(50)')

    from @XML.nodes('//*') x(y);

    That's sample XML, but it pulls name-value pairs from it, and that sounds like what you need.

    If you want attribute values as well, do this

    select r.value('local-name(.)','varchar(100)') as ElementName,

    r.value('text()[1]','varchar(100)') as ElementValue,

    a.value('local-name(.)','varchar(100)') as AttributeName,

    a.value('.','varchar(100)') as AttributeValue

    from @XML.nodes('//*') as x(r)

    outer apply r.nodes('@*') as y(a)

    ____________________________________________________

    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
  • Hi Guys,

    Please help me out!! AsAp...

    I have a unstructured XML files …. Means … each file can have different no of NODES…

    So please let me know how I can upload this file if I do not know what fields are coming in this file.

    I need query to upload unstructured XMl file in sql server2008

    Thanks

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

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