Parsing an XML field within a table

• sacrokingsj55

  SSC Veteran

  Points: 269

  More actions

  February 12, 2021 at 5:10 pm

  #3844300

  I am trying to parse a field that is XML in a table and insert into a table. Where would be a good example to do this> I have attached the values from that field in a notepad++ file.

  Attachments:

  You must be logged in to view attached files.

• Ed Wagner

  SSC Guru

  Points: 287026

  More actions

  February 12, 2021 at 11:26 pm

  #3844429

  You'll first want to import the file into a table as an XML column. Then you can query the data from the XML in the table. Here's an example using the dbo.Staging table with a single column named Entries.

   SELECT Name = fld.Doc.value('(@name)[1]', 'varchar(128)')
     FROM dbo.Staging i
       OUTER APPLY i.Entries.nodes('/entry/field') fld (Doc);
  

  Hope this helps.

  Tally Tables - Performance Personified
  String Splitting with True Performance
  Best practices on how to ask questions