SQL Server : extract value from XML column

Question

I have a xml column Formdata in a table pt_formdata in SQL Server. This is how my xml looks like:

<?csps doctype="dataset" version="1.0.0"?> 
<csps:root formtype="AFTeleIntake" guid="ecde2347-ae24-4bd0-a4bd-60e11ca38ed9" xmlns:csps="http://tempuri.org/AFTeleIntake.xsd">
 <Questions>
  <row>
   <ReferringProvider>Abc</ReferringProvider> 
   <PrimaryCareProvider>Bcd</PrimaryCareProvider> 
   <TelephoneContactDate>07-Nov-2013</TelephoneContactDate> 
   <AFType>type1</AFType> 
  </row>
 </Questions>
</csps:root>

I want to create a SQL query to fetch AFType from this xml column. I'm pretty new to SQL Server, and I have tried lots of functions.

For example:

SELECT 
   PT_FormData.Formdata.query('declare namespace x="http://tempuri.org/AFTeleIntake.xsd";
      (/x:csps/x:Questions/x:row/x:AFType)') AS Description 
FROM database.[dbo].[PT_FormData]

but the output column empty.How can I get around what I want?

Any help will be appreciated

Thanks Tina

marc_s · Accepted Answer · 2013-11-08 20:26:55Z

1

Try this:

;WITH XMLNAMESPACES('http://tempuri.org/AFTeleIntake.xsd' AS csps)
SELECT
    Formdata.value('(/csps:root/Questions/row/AFType)[1]', 'varchar(50)')
FROM 
  PT_FormData

answered Nov 8, 2013 at 20:26

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Avitus · Accepted Answer · 2013-11-08 20:23:04Z

Here is an example on how to get the AFType from your above case:

DECLARE @x XML
SET @x = '<?csps doctype="dataset" version="1.0.0"?> 
<csps:root formtype="AFTeleIntake" guid="ecde2347-ae24-4bd0-a4bd-60e11ca38ed9" xmlns:csps="http://tempuri.org/AFTeleIntake.xsd">
 <Questions>
  <row>
   <ReferringProvider>Abc</ReferringProvider> 
   <PrimaryCareProvider>Bcd</PrimaryCareProvider> 
   <TelephoneContactDate>07-Nov-2013</TelephoneContactDate> 
   <AFType>type1</AFType> 
  </row>
  <row>
   <ReferringProvider>Abc</ReferringProvider> 
   <PrimaryCareProvider>Bcd</PrimaryCareProvider> 
   <TelephoneContactDate>07-Nov-2013</TelephoneContactDate> 
   <AFType>type2</AFType> 
  </row>
 </Questions>
</csps:root>'


select t.c.value('AFType[1]', 'varchar(50)')
from @x.nodes('declare namespace csps="http://tempuri.org/AFTeleIntake.xsd"; csps:root/Questions/row') t(c)

Collectives™ on Stack Overflow

SQL Server : extract value from XML column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related