I change a little the sample , because it wasn't a valid xmldeclare @xml as xmlset @xml=' <FieldS> <Content> <ContentValue xmlns:p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> <p1:KeyValueOfstringstring> <p1:Key>1</p1:Key> <p1:Value>U.S. citizen (or U.S. National)</p1:Value> </p1:KeyValueOfstringstring> <p1:KeyValueOfstringstring> <p1:Key>2</p1:Key> <p1:Value>Eligible noncitizen</p1:Value> </p1:KeyValueOfstringstring> <p1:KeyValueOfstringstring> <p1:Key>3</p1:Key> <p1:Value>Neither 1 nor 2, not eligible</p1:Value> </p1:KeyValueOfstringstring> </ContentValue> </Content> </FieldS>' ;with xmlnamespaces ('http://schemas.microsoft.com/2003/10/Serialization/Arrays' as p1) select t.u.value('(./p1:Key)[1]','varchar(100)') as k ,t.u.value('(./p1:Value)[1]','varchar(100)') as Value from @xml.nodes('/FieldS/Content/ContentValue/p1:KeyValueOfstringstring') t(u)
the result :k Value1 U.S. citizen (or U.S. National)2 Eligible noncitizen3 Neither 1 nor 2, not eligible
Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mutsabinWeb