Your OPENXML query doesn't work because the node value contains white-space, which is not getting trimmed. Since you're only taking the first 5 characters, you'll get a string containing nothing by white-space. Your .nodes() query doesn't work because you're getting the PolicyType nodes, and then trying to find the third PolicyType node within that node. Try this:
DECLARE @xmlPolicyType XML
SET @xmlPolicyType= REPLACE('<?xml version="1.0" encoding="utf-16"?>
<NewDataSet>
<PolicyType>
MED
</PolicyType>
<PolicyType>
SPORT
</PolicyType>
</NewDataSet>
','utf-16','UTF-8');
DECLARE @PolicyTypes TABLE
(
PolicyType char(5)
);
INSERT INTO @PolicyTypes
(
PolicyType
)
SELECT
LTrim(Replace(Replace(Replace(
T.PolicyType.value('.', 'varchar(120)')
, Char(13), ''), Char(10), ''), Char(9), ''))
FROM
@xmlPolicyType.nodes('/NewDataSet/PolicyType') As T (PolicyType)
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
