Help with XML

[byka]

Could you help figure out why my code not working?

DECLARE @xmlPolicyType XML
SET @xmlPolicyType= REPLACE('

				MED  
			
			
				SPORT
			
			
			','utf-16','UTF-8')

DECLARE @xmlHandle INT
IF NOT @xmlPolicyType IS NULL
BEGIN

					DECLARE @PolicyTypes TABLE 
						( 
						   PolicyType	CHAR(5) 
						) 

						
					/\* Create the XmlDocument  \*/
					  EXEC sp\_xml\_preparedocument @xmlHandle output, @xmlPolicyType 
					    
					/\*   Use the OPENXML method to query the XmlDocument starting at  /NewDataSet/PolicyType node. \*/ 
						--INSERT INTO @PolicyTypes 
						SELECT   PolicyType
						FROM OPENXML (@xmlHandle, '/NewDataSet/PolicyType',1) 
						WITH ( PolicyType CHAR(5) '@PolicyType'  )
					 
						/\* Remove the document from memory \*/ 
						EXEC sp\_xml\_removedocument @xmlHandle
					 
					 
					 
				 
					 SELECT t.value('(PolicyType/text())\[2\]','nvarchar(120)')AS PolicyType 
					 FROM @xmlPolicyType.nodes('/NewDataSet/PolicyType')AS TempTable(t)

[PIEBALDconsult]

What about it isn't working? Is there an error message? What database system?

You'll never get very far if all you do is follow instructions.

[byka]

Values is not selected

[Richard Deeming]

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