XML file data read through Stored Procedure

• madhusudhana.r

  SSC Veteran

  Points: 289

  More actions

  March 27, 2009 at 8:18 am

  #130153

  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

• GSquared

  SSC Guru

  Points: 260824

  More actions

  March 27, 2009 at 9:55 am

  #967558

  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

• Mark Cowne

  One Orange Chip

  Points: 26979

  More actions

  March 27, 2009 at 10:10 am

  #967570

  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

• gmsharma11

  Valued Member

  Points: 58

  More actions

  January 5, 2015 at 12:27 pm

  #1767852

  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