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
,
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
yosiasz
February 8, 2019, 12:58am
6
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