read a xml data stored in a field of a table

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

General SQL Server Forums

New to SQL Server Programming

read a xml data stored in a field of a table

Author

Topic

anup23
Starting Member

19 Posts

Posted - 2011-11-10 : 02:19:32

I want read xml data from a field of a table and the vield is nvarchar. After that need to copy specific nodes value and put it in another table. Also need to use triggers in this.

Can anyone help me!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 03:00:00

you dont need trigger for this unless you've to associate this to DML action like insert,update
Anyways, what you need to do is to use OPENXML if you're below sql 2005. If you're using sql 2005 or above, you can cast nvarchar field to new xml data type which is available from 2005 onwards and then use .nodes() and .value functions to shred off the values and populate other table
If you need more details, post some sample data and explain what you want to get out of it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anup23
Starting Member

19 Posts

Posted - 2011-11-10 : 03:06:51

a table is there environment with column datamodel which contains the xml and the datamodel is nvarchar(max)
and xml looks like this

<?xml version="1.0" encoding="UTF-8"?>
<Environment Created="11/10/2011 10:42:31 AM" IsCurrent="False" Name="test" Id="6501243f-e80a-42b5-b846-7c617c216a1e">
<Server ServerTypeName="Database" ServerTypeId="1" Name="V-ANUKUM" Id="bedce5bd-2f10-4564-b232-a2adee700b37">
<Type Name="Sql Server"/>
<Context InstanceName="ANUKUM" DatabaseName="947" ConnectionString="Data Source=V-ANUKUM;Initial Catalog=DynamicsAX_947;Integrated Security = true"/>
</Server>
<Server ServerTypeName="AO" ServerTypeId="2" Name="V-ANU@27\01" Id="a69d72ff-411c-4f59-a483-2e1091c95714">
<Type Name="Application Object Server"/>
<Context/>
</Server>
<Server ServerTypeName="TERMINAL" ServerTypeId="6" Name="ANUKUM" Id="687d845b-9e0d-4c9a-7758481e05fb">
<Type Name="TERMINAL Server"/>
<Context/>
</Server>
</Environment>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 03:18:48

ok. then you can use query like below to get required details out(assuming you're on sql 2005 and above)


declare @x nvarchar(max)
set @x='<?xml version="1.0" ?>
<Environment Created="11/10/2011 10:42:31 AM" IsCurrent="False" Name="test" Id="6501243f-e80a-42b5-b846-7c617c216a1e">
<Server ServerTypeName="Database" ServerTypeId="1" Name="V-ANUKUM" Id="bedce5bd-2f10-4564-b232-a2adee700b37">
<Type Name="Sql Server"/>
<Context InstanceName="ANUKUM" DatabaseName="947" ConnectionString="Data Source=V-ANUKUM;Initial Catalog=DynamicsAX_947;Integrated Security = true"/>
</Server>
<Server ServerTypeName="AO" ServerTypeId="2" Name="V-ANU@27\01" Id="a69d72ff-411c-4f59-a483-2e1091c95714">
<Type Name="Application Object Server"/>
<Context/>
</Server>
<Server ServerTypeName="TERMINAL" ServerTypeId="6" Name="ANUKUM" Id="687d845b-9e0d-4c9a-7758481e05fb">
<Type Name="TERMINAL Server"/>
<Context/>
</Server>
</Environment>'

select t.u.value('../@Created[1]','datetime') as CreatedDate,
t.u.value('../@IsCurrent[1]','varchar(5)') as IsCurrent,
t.u.value('../@Name[1]','varchar(55)') as Name,
t.u.value('./@ServerTypeName[1]','varchar(55)') as ServerTypeName,
t.u.value('./@ServerTypeId[1]','int') as ServerTypeId,
t.u.value('./@Name[1]','varchar(55)') as ServerName,
t.u.value('./Type[1]/@Name[1]','varchar(55)') as Name,
t.u.value('./Context[1]/@InstanceName[1]','varchar(55)') as InstanceName,
t.u.value('./Context[1]/@DatabaseName[1]','varchar(55)') as DatabaseName
from (select cast(@x as xml) AS x)m
cross apply m.x.nodes('/Environment/Server')t(u)

output
-------------------------------------------
CreatedDate	IsCurrent	Name	ServerTypeName	ServerTypeId	ServerName	Name	InstanceName	DatabaseName
2011-11-10 10:42:31.000	False	test	Database	1	V-ANUKUM	Sql Server	ANUKUM	947
2011-11-10 10:42:31.000	False	test	AO	2	V-ANU@27\01	Application Object Server	NULL	NULL
2011-11-10 10:42:31.000	False	test	TERMINAL	6	ANUKUM	TERMINAL Server	NULL	NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anup23
Starting Member

19 Posts

Posted - 2011-11-10 : 04:12:57

Thanks for the reply .. the colum is nvarchar and this coulm field changes everytime ed do some operation .. I mean xml nodes will be same but the values will change .. so I cant hard code the xml .
Its like we have to take the data drom that coulmn field and then put the specific nodes in the other table ...
And thats y I thought use trigger...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 04:16:39

quote:
Originally posted by anup23

Thanks for the reply .. the colum is nvarchar and this coulm field changes everytime ed do some operation .. I mean xml nodes will be same but the values will change .. so I cant hard code the xml .
Its like we have to take the data drom that coulmn field and then put the specific nodes in the other table ...
And thats y I thought use trigger...

I've also not hardcoded the xml. i've just shown an example by putting it in a variable. you can use a table column instead if you want. the only places i hardcoded was node names which according to you will be static so thats not a problem.
I've also used nvarchar field itself in above code. it just needs one casting step to make it xml and then use xml methods

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anup23
Starting Member

19 Posts

Posted - 2011-11-10 : 04:24:31

Sorry I didnt get you , do you mean I have to use this
set @x= select [datamodel] from [Diag].[dbo].[ENVIRONMENT].

anup23
Starting Member

19 Posts

Posted - 2011-11-10 : 05:21:18

Actually I want to get specific nodes and values from that field(xml) and put it into a different table and it should update the other table everytime the parent table is changed , i mean when the xmal nodes value changes...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 06:32:01

quote:
Originally posted by anup23

Sorry I didnt get you , do you mean I have to use this
set @x= select [datamodel] from [Diag].[dbo].[ENVIRONMENT].

nope
you can directly use like


update p
set p.CreatedDate = q.CreatedDate,
p.IsCurrent = q.IsCurrent,
..... other columns here
from othertable p
inner join
(
select t.u.value('../@Created[1]','datetime') as CreatedDate,
t.u.value('../@IsCurrent[1]','varchar(5)') as IsCurrent,
t.u.value('../@Name[1]','varchar(55)') as Name,
t.u.value('./@ServerTypeName[1]','varchar(55)') as ServerTypeName,
t.u.value('./@ServerTypeId[1]','int') as ServerTypeId,
t.u.value('./@Name[1]','varchar(55)') as ServerName,
t.u.value('./Type[1]/@Name[1]','varchar(55)') as Name,
t.u.value('./Context[1]/@InstanceName[1]','varchar(55)') as InstanceName,
t.u.value('./Context[1]/@DatabaseName[1]','varchar(55)') as DatabaseName
from (select cast(REPLACE([datamodel],'encoding="UTF-8"','') as xml) as datamodel from [Diag].[dbo].[ENVIRONMENT])m
cross apply t.datamodel.nodes('/Environment/Server')t(u)
)q
on q.linkingcolumn = p.linkingcolumn

please change code in blue with actual column names

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anup23
Starting Member

19 Posts

Posted - 2011-11-10 : 23:42:20

Thanks a lot for the help .... It helped me a lot to complete my task ..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-11 : 03:06:00

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anup23
Starting Member

19 Posts

Posted - 2011-11-16 : 03:35:15

Hi , Kindly let me know if you are there .. have a doubt ..

Thanks
Anup

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 03:53:54

quote:
Originally posted by anup23

Hi , Kindly let me know if you are there .. have a doubt ..

Thanks
Anup

Post it please

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anup23
Starting Member

19 Posts

Posted - 2011-11-16 : 04:00:34

Thnaks . . I have written a insert trigger taking inputs from the one which u suggested earlier for reading xml. Now I have to include for update too .. Condition is:
1> if some xml node is deleted from the table the isActive should update as FALSE in the new table
2> if some new node appears then it should be true in the new table.

Here is the trigger :
ALTER TRIGGER [dbo].[EnvironmentTrigger]
ON [dbo].[ENVIRONMENT]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @ENVID BIGINT
DECLARE @x nvarchar(max)
-- Insert statements for trigger here
SELECT @ENVID=i.ID FROM Inserted i

SELECT top 1 @x = i.DATAMODEL FROM Inserted i

Insert into ENVIRONMENT_SERVERMAP (ENVIRONMENT_ID,SERVER_TYPE_ID,FQDN,INSTANCE_NAME,IS_ACTIVE,LAST_DISCOVERY_DATE)
select
@ENVID,
t.u.value('./@ServerTypeId[1]','int') as SERVER_TYPE_ID,
t.u.value('./@Name[1]','varchar(55)') as FQDN,
t.u.value('./Context[1]/@InstanceName[1]','varchar(55)') as INSTANCE_NAME,
1 as IS_ACTIVE,
getdate() as LAST_DISCOVERY_DATE
from (select cast(REPLACE(@x,'encoding="UTF-8"','') as xml) as x) m

cross apply m.x.nodes('/Environment/Server')t(u)

END

anup23
Starting Member

19 Posts

Posted - 2011-11-16 : 04:02:12

Comaprision should be done based on the xml which I am passing

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 04:59:53

quote:
Originally posted by anup23

Comaprision should be done based on the xml which I am passing

where are you passing xml? is it in insert procedure for table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anup23
Starting Member

19 Posts

Posted - 2011-11-16 : 05:05:33

xml is there in the table field (datamodel).
And in that only this trigger is written.

anup23
Starting Member

19 Posts

Posted - 2011-11-16 : 05:06:39

SELECT top 1 @x = i.DATAMODEL FROM Inserted i
here i am reading the xml from the datamodel field.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 05:46:41

quote:
Originally posted by anup23

SELECT top 1 @x = i.DATAMODEL FROM Inserted i
here i am reading the xml from the datamodel field.

this has an issue
here you're assuming that only one row will be inserted at a time which may not be case always

you cant store result in a variable like this. you need to use a table variable instead or use a join with inserted table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anup23
Starting Member

19 Posts

Posted - 2011-11-16 : 05:51:16

this trigger is working for insert all i have to do is for update with the conditions..

anup23
Starting Member

19 Posts

Posted - 2011-11-16 : 05:55:54

whenever the xml is inserted in the table field its get updated in the other table..Need to write trigger for After Update if any xml data changes ... or deleted and accordingly set the isActive field

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources