Column with XML to fields

1

I have a SQL server database with some data fields that have XML formatted data in the field and I want to export this to a new table or view with the XML data into columns...

Table Name: MyHistory
Field Names: AccountNo, Allergies
Example of data in field XML Field (Allergies):

image
image.png780×21 1.63 KB

How can I use SQL to take the data above and get a Database with these columns?

AccountNo, Allergy, Reaction, Onset

2

please post xml as text and not as picture. we dont have technology to extract from pix yet :wink:

3

I tried and it seems to trim all the formatting - FIgured it out :slight_smile:

<grdAllergies><Row><Allergy>celebrex, ceftin</Allergy><ID></ID><NDCIDs></NDCIDs><RxNorm></RxNorm><GroupID></GroupID><Reaction></Reaction><OnsetDate></OnsetDate><Status></Status></Row></grdAllergies>

4

AccountNo nowhere to be seen? do you have xml allergies? :wink:

5 
DECLARE @contagion xml;
DECLARE @strCar varchar(max);
SET @contagion =
'<grdAllergies>
   <Row>
      <Allergy>celebrex, ceftin</Allergy>
      <ID />
      <NDCIDs />
      <RxNorm />
      <GroupID />
      <Reaction />
      <OnsetDate />
      <Status />
   </Row>
</grdAllergies>'


DECLARE @hdoc INT

Exec sp_xml_preparedocument @hdoc OUTPUT, @contagion

SELECT *
FROM OPENXML (@hdoc,'/grdAllergies/Row',2)
WITH (	
      Allergy VARCHAR(100),
	  Reaction VARCHAR(100),
	  OnsetDate datetime
	  )
6

Thank you, sorry about the missing AccountNo... :slight_smile:

If I have a table with thousands of records with AccountNo and Different Allergy details, can I pull from that table to create a new one?

|AccountNo|Allergies|
|---|---|
|66914|<grdAllergies><Row><Allergy>celebrex, ceftin</Allergy><ID></ID><NDCIDs></NDCIDs><RxNorm></RxNorm><GroupID></GroupID><Reaction></Reaction><OnsetDate></OnsetDate><Status></Status></Row></grdAllergies>|
|54556|<grdAllergies><Row><Allergy>Ibuprofin</Allergy><ID></ID><NDCIDs></NDCIDs><RxNorm></RxNorm><GroupID></GroupID><Reaction></Reaction><OnsetDate></OnsetDate><Status></Status></Row></grdAllergies>|
|15688|<grdAllergies><Row><Allergy>Vitamin C</Allergy><ID></ID><NDCIDs></NDCIDs><RxNorm></RxNorm><GroupID></GroupID><Reaction></Reaction><OnsetDate></OnsetDate><Status></Status></Row></grdAllergies>|
|45684|<grdAllergies><Row><Allergy>Penacillan</Allergy><ID></ID><NDCIDs></NDCIDs><RxNorm></RxNorm><GroupID></GroupID><Reaction></Reaction><OnsetDate></OnsetDate><Status></Status></Row></grdAllergies>|
|45881|<grdAllergies><Row><Allergy>Celebrex</Allergy><ID></ID><NDCIDs></NDCIDs><RxNorm></RxNorm><GroupID></GroupID><Reaction></Reaction><OnsetDate></OnsetDate><Status></Status></Row></grdAllergies>|
7

yes you can please provide the data as sample like this

create table #Chimera(AccountNo int, Allergies xml)
insert into  #Chimera
select 66914, <grdAllergies><Row><Allergy>celebrex, ceftin</Allergy><ID></ID><NDCIDs></NDCIDs><RxNorm></RxNorm><GroupID></GroupID><Reaction></Reaction><OnsetDate></OnsetDate><Status></Status></Row></grdAllergies>

and provide all of the sample data.

8

Sorry, I am not following what you need exactly... I already have the database with the two fields and the data in the fields, I am just trying to take that data and parse it from the XML to fields

9

yes but if you want us to help you, you have to provide us sample data. that way you get faster answer. otherwise I have to create the sample data for you because I do not have remote access to your database.

10

like this

create table #Chimera(AccountNo int, Allergies xml)

insert into  #Chimera
select 66914,'<grdAllergies><Row><Allergy>celebrex, ceftin</Allergy><ID></ID><NDCIDs></NDCIDs><RxNorm></RxNorm><GroupID></GroupID><Reaction></Reaction><OnsetDate></OnsetDate><Status></Status></Row></grdAllergies>' union
select 54556,'<grdAllergies><Row><Allergy>Ibuprofin</Allergy><ID></ID><NDCIDs></NDCIDs><RxNorm></RxNorm><GroupID></GroupID><Reaction></Reaction><OnsetDate></OnsetDate><Status></Status></Row></grdAllergies>' union
select 15688,'<grdAllergies><Row><Allergy>Vitamin C</Allergy><ID></ID><NDCIDs></NDCIDs><RxNorm></RxNorm><GroupID></GroupID><Reaction></Reaction><OnsetDate></OnsetDate><Status></Status></Row></grdAllergies>' union
select 45684,'<grdAllergies><Row><Allergy>Penacillan</Allergy><ID></ID><NDCIDs></NDCIDs><RxNorm></RxNorm><GroupID></GroupID><Reaction></Reaction><OnsetDate></OnsetDate><Status></Status></Row></grdAllergies>' union
select 45881,'<grdAllergies><Row><Allergy>Celebrex</Allergy><ID></ID><NDCIDs></NDCIDs><RxNorm></RxNorm><GroupID></GroupID><Reaction></Reaction><OnsetDate></OnsetDate><Status></Status></Row></grdAllergies>' 

select AccountNo, 
       Allergies.value('(//*[local-name()="Allergy"])[1]', 'nvarchar(max)')  as Allergy,
       Allergies.value('(//*[local-name()="Reaction"])[1]', 'nvarchar(max)')  as Reaction,
	   case Allergies.value('(//*[local-name()="OnsetDate"])[1]', 'datetime')
	      when  '1900-01-01 00:00:00.000' then null
		  else Allergies.value('(//*[local-name()="OnsetDate"])[1]', 'datetime')
	   end OnsetDate 
  From #Chimera
  

drop table #Chimera
11

I am so sorry, got interrupted with a phone call.. I see, let me see if I can make that work! THANK YOU!!