xml make it dynamic - SQL Server Forums

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

xml make it dynamic

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-20 : 14:06:14

I want to get documnetid and partnerid. In my criteria i have @type hard-coded however I want it to be dynamic.
I want to be able to replace from this Date.value('(/Dates/Date[@Type="Export-Ad"]/@Value)[1]', 'varchar(50)')
to Date.value('(/Dates/Date[@Type="@type"]/@Value)[1]', 'varchar(50)'). HOpefully makes sense.

I also have a entitytype table with all the types if it helps.

declare @entitytype table (typename varchar(30))
insert @entitytype
select 'Export-Ad' union all
select 'Import-Ad' union all
select 'Emails'

select * from @entitytype

declare @PartnerDocument table (documentid int, partnerid int, date xml)

insert @PartnerDocument
select 1, 20, '<Dates>
<Date Type="Export-Ad" Value="2011-10-19T18:16:09.880" />
</Dates>' union all
select 2, 21, '<Dates>
<Date Type="Import-Ad" Value="2010-11-19T18:16:09.880" />
</Dates>' union all
select 3, 22, '<Dates>
<Date Type="Emails" Value="2009-11-19T18:16:09.880" />
</Dates>'

select * from @PartnerDocument
/*
SELECT DocumentID, partnerid FROM @PartnerDocument
WHERE Date.value('(/Dates/Date[@Type="Export-Ad"]/@Value)[1]', 'varchar(50)') IS NOT NULL
*/

declare @type varchar(30)
declare @DocumentID int
declare @partnerid int

DECLARE Employee_Cursor CURSOR FOR
SELECT DocumentID, partnerid FROM @PartnerDocument WHERE Date.value('(/Dates/Date[@Type="Export-Ad"]/@Value)[1]', 'varchar(50)') IS NOT NULL
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @DocumentID, @partnerid
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @DocumentID, @partnerid

FETCH NEXT FROM Employee_Cursor into @DocumentID, @partnerid
END
CLOSE Employee_Cursor
DEALLOCATE Employee_Cursor

nathans
Aged Yak Warrior

938 Posts

Posted - 2011-10-20 : 21:14:23

I think I understand... let me know if this is in right direction:


declare @DateType varchar(25);
set @DateType = 'Emails';

-- if only ever 1 Date element per document:
select	DocumentId, PartnerId
from	@PartnerDocument pd
where	pd.[date].exist('(Dates/Date)[1][@Type = sql:variable("@DateType")]') = 1;


-- if > 1 Date element:
select	DocumentId, PartnerId
from	@PartnerDocument pd
cross
apply	pd.[date].nodes('Dates') p(n)
where	p.n.exist('Date[@Type = sql:variable("@DateType")]') = 1;

Nathan Skerl

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 00:32:36

[code]
declare @entitytype table (typename varchar(30))
insert @entitytype
select 'Export-Ad' union all
select 'Import-Ad' union all
select 'Emails'

select * from @entitytype

declare @PartnerDocument table (documentid int, partnerid int, date xml)

insert @PartnerDocument
select 1, 20, '<Dates>
<Date Type="Export-Ad" Value="2011-10-19T18:16:09.880" />
</Dates>' union all
select 2, 21, '<Dates>
<Date Type="Import-Ad" Value="2010-11-19T18:16:09.880" />
</Dates>' union all
select 3, 22, '<Dates>
<Date Type="Emails" Value="2009-11-19T18:16:09.880" />
</Dates>'

select *
from (select t.u.value('./@Type[1]','varchar(100)') as [type],
t.u.value('./@Value[1]','datetime') AS [dateval]
from @PartnerDocument p
cross apply [date].nodes('/Dates/Date')t(u))m
inner join @entitytype et
on et.typename = m.type
[/code]

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

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-24 : 15:55:48

Thanks v

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 00:17:22

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources