XML Values

Hi, What would be the best practice or recommended approach to extract the value or string "dateTime"?

<?xml version="1.0" encoding="utf-8"?> <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <GetCurrentTimeResponse xmlns="http://smartbear.com"> <GetCurrentTimeResult>dateTime</GetCurrentTimeResult> </GetCurrentTimeResponse> </soap:Body> </soap:Envelope>

Google search
With the terms

Hope this helps
,:grinning::grinning:

what is generating this xml? there seems to be some data missing on GetCurrentTimeResponse xmlns="http://smartbear.com" ?

declare @xmldata as xml = 
'<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetCurrentTimeResponse xmlns="http://smartbear.com">
      <GetCurrentTimeResult>dateTime</GetCurrentTimeResult>
    </GetCurrentTimeResponse>
  </soap:Body>
</soap:Envelope>'

--Method 1
;WITH XMLNAMESPACES(
    'http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
    'http://www.w3.org/2001/XMLSchema' AS s,
	'http://www.w3.org/2001/XMLSchema-instance' as si, 
    DEFAULT 'http://smartbear.com'
)
SELECT r.value('GetCurrentTimeResult[1]', 'varchar(max)') as val
  FROM @xmldata.nodes('/*:Envelope/*:Body/*:GetCurrentTimeResponse') AS A(r)

  --Method 2
;WITH XMLNAMESPACES(
    'http://schemas.xmlsoap.org/soap/envelope/' AS "soap",
    'http://www.w3.org/2001/XMLSchema' AS s,
	'http://www.w3.org/2001/XMLSchema-instance' as si, 
    DEFAULT 'http://smartbear.com'
)
SELECT r.value('GetCurrentTimeResult[1]', 'varchar(max)')
  FROM @xmldata.nodes('//*:GetCurrentTimeResponse') AS A(r)

--Method 3
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmldata, '<root xmlns:smartbear="http://smartbear.com"/>';  

SELECT    *  
  FROM OPENXML (@hdoc, '//smartbear:GetCurrentTimeResponse',2)  
  WITH ([smartbear:GetCurrentTimeResult]  varchar(50));  
				    
-- Remove the internal representation.  
exec sp_xml_removedocument @hdoc;  
2 Likes

There are multiple methods of parsing XML but I just want to see which is the preferred method. Thank You

I am not sure which one would be better recommended. I have read openxml has some overhead. parsing xml in general has overhead anyways.

What would be best thing for you to do is create a huge xml file and do some bench marking to see which one has the best performance. Others can chime in with more benchmarking experience with xml parsing.

This is a great read

http://www.sqlservercentral.com/articles/Tally+Table/72993/

1 Like