Get XML values from XML Column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get XML values from XML Column

Author

Topic

srujanavinnakota
Starting Member

34 Posts

Posted - 2013-08-23 : 18:22:40

I have an table with XML column,which has below XML. I need to extract the below xml and store in to a table.

<SchemaType xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas. contract.org/2004/07/xyz">
<Year>1999</Year>
<Fields>
<Comments>Student's Citizenship Status</Comments>
<DataType>String</DataType>
<DbLocation>IsirData</DbLocation>
<Code i:nil="true" />
<Number i:nil="true" />
<Code>Citizen</FieldCode>
<Number>17</FieldNumber>
<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>
Please give me the query to extract above xml .

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-24 : 00:31:04

I change a little the sample , because it wasn't a valid xml



declare @xml as xml
set @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	Value
1	U.S. citizen (or U.S. National)
2	Eligible noncitizen
3	Neither 1 nor 2, not eligible

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources