Extract XML values using SQL

• chris.mcbain

  SSChasing Mays

  Points: 619

  More actions

  March 7, 2011 at 4:55 am

  #157472

  Hi

  I am trying to extract values from an XML file using SQL. My XML contains nodes named "forename" and "surname" which appears twice in the XML. The first occurence is blank but the second contains the actual names.

  My query currently looks only at the first occurence of <forename> and <surname> so in my results I will see "UNKNOWN" rather than the actual names.

  My question is, how do I extract the value from both the first and second <forename> and <surname> nodes?

  Using the SQL below to do this:

  CAST(CAST(m.filedata as VARCHAR(MAX)) AS XML).value('(//*:Surname)[1]', 'NVarchar(50)') ,

  CAST(CAST(m.filedata as VARCHAR(MAX)) AS XML).value('(//*:Forename)[1]', 'NVarchar(50)')

  This will identify the "Surname" node and then give me the value inside.

  The XML comes from a source system which I cannot change.

  Appreciate any help.

• Yogesh Potdar

  SSC Enthusiast

  Points: 192

  More actions

  March 7, 2011 at 5:43 am

  #1295265

  Just check if you can do something like this

  DECLARE @hDoc INT,

  @XmlString AS VARCHAR(MAX)

  SET @XmlString = '<XmlRoot>

  <TableName Id="1" FirstName="A" LastName="L" />

  <TableName Id="2" LastName="M" />

  <TableName Id="3" FirstName="C" LastName="N" />

  <TableName Id="4" FirstName="D" LastName="L" />

  </XmlRoot>';

  EXEC SP_XML_PREPAREDOCUMENT @hDoc OUTPUT, @XmlString;

  SELECTId,

  FirstName,

  LastName

  INTO #TempTable

  FROM OPENXML(@hDoc, 'XmlRoot/TableName')

  WITH

  (

  IdINT,

  FirstNameVARCHAR(100),

  LastNameVARCHAR(100)

  )

  SELECT * FROM #TempTable

  GO

  SELECT * FROM #TempTable

  WHERE FirstName IS NOT NULL;

  GO

  DROP TABLE #TempTable

  GO