need help parsing xml

• koolme_85

  Ten Centuries

  Points: 1153

  More actions

  February 19, 2020 at 9:45 pm

  #3727210

  Scenario 1 works just fine

  DECLARE @XMLT XML = '<Header>

  <ReportDate>10032017</ReportDate>

  <ReportTime>140607</ReportTime>

  <Preamble>TWA1</Preamble>

  <ARFVersion>07</ARFVersion>

  </Header>'

  SELECT @XMLT AS TheXML;

  select

  Header.DT.value('ReportDate[1]' ,'VARCHAR(10)') AS ReportDate

  ,Header.DT.value('ReportTime[1]' ,'VARCHAR(10)') AS ReportTime

  ,Header.DT.value('Preamble[1]' ,'VARCHAR(10)') AS Preamble

  ,Header.DT.value('ARFVersion[1]' ,'VARCHAR(10)') AS ARFVersion

  FROM @XMLT.nodes('Header') AS Header(DT);

   

  but i have xml that has data like (highlighted in bold) and i get xml parsing error

  (XML parsing: line 6, character 21, A string literal was expected)

   

  DECLARE @XMLT XML = '<Header>

  <ReportDate>10032017</ReportDate>

  <ReportTime>140607</ReportTime>

  <Preamble>TWA1</Preamble>

  <ARFVersion>07</ARFVersion>

  <Evaluation code=\"P\"/>

  </Header>'

   

• Site Owners

  SSC Guru

  Points: 80320

  More actions

  February 20, 2020 at 10:10 pm

  #3727510

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  February 21, 2020 at 3:48 pm

  #3727727

  Please do not cross post.  Please respond on this thread https://www.sqlservercentral.com/forums/topic/need-help-parsing-xml-3#post-3727725

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA