Need to insert a column into my xml

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

Need to insert a column into my xml

Author

Topic

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-11 : 13:37:51

I want to get the TestID into my xml. I can join on startdate and enddate and get the top 1.

result:
<KPICounts>
<KPICount SiteID="2" testID = "1" TestGoupID="1" GroupName="Production" Name="ga:goal12ConversionRate" DataType="percent" Value="0.0" />
<KPICount SiteID="2" testID = "1" TestGoupID="2" GroupName="Beta" Name="ga:goal12ConversionRate" DataType="percent" Value="0.0" />
<KPICount SiteID="2" testID = "1" TestGoupID="3" GroupName="Control" Name="ga:goal12ConversionRate" DataType="percent" Value="0.0" />
</KPICounts>

Currently the result is:

<KPICounts>
<KPICount SiteID="2" TestGoupID="1" GroupName="Production" Name="ga:goal12ConversionRate" DataType="percent" Value="0.0" />
<KPICount SiteID="2" TestGoupID="2" GroupName="Beta" Name="ga:goal12ConversionRate" DataType="percent" Value="0.0" />
<KPICount SiteID="2" TestGoupID="3" GroupName="Control" Name="ga:goal12ConversionRate" DataType="percent" Value="0.0" />
</KPICounts>

------------------------------------------------------------------------------------------------------------------------------------------------------------

declare @test table (testid int, startdate datetime, enddate datetime)
declare @feedstaging table (feedstagingid int, feedid int, data xml, startdate datetime, enddate datetime)

insert @test
select 1, '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000' union all
select 2, '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000' union all
select 3, '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000'

insert @feedstaging
select 1, 2, '<Datas>
<Data SiteID="2" TestGoupID="1" GroupName="Production">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
<Data SiteID="2" TestGoupID="2" GroupName="Beta">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
<Data SiteID="2" TestGoupID="3" GroupName="Control">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
</Datas>', '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000'

declare @xml xml
DECLARE @StartDate AS DATETIME = '2011-09-27 00:00:00.000'
DECLARE @ENDDATE AS DATETIME = '2011-09-27 23:59:59.000'
DECLARE @FEEDID AS INT = 2

SELECT @XML = data FROM @FeedStaging WHERE StartDate = @StartDate AND EndDate = @EndDate AND FeedID = @FeedID

SELECT KPICount.n.value('@SiteID', 'int') AS SiteID,
KPICount.n.value('@TestGoupID', 'int') AS TestGoupID,
KPICount.n.value('@GroupName', 'VARCHAR(30)') AS GroupName,
aggr.n.value('@name', 'VARCHAR(100)') AS Name,
aggr.n.value('@type', 'VARCHAR(50)') AS DataType,
aggr.n.value('@value', 'VARCHAR(100)') AS Value

FROM @XML.nodes('/Datas/Data') AS KPICount(n)
CROSS APPLY n.nodes('dxp-aggregates/dxp-metric') AS aggr(n)
FOR XML AUTO, TYPE, ROOT('KPICounts')

------------------------------------------------------------------------------------------------------------------------------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 14:13:52

why do you need all this? you can follow same pattern i gave for last post
see

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=166224


declare @test table (testid int, startdate datetime, enddate datetime)
declare @feedstaging table (feedstagingid int, feedid int, data xml, startdate datetime, enddate datetime)

insert @test
select 1, '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000' union all
select 2, '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000' union all
select 3, '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000'


insert @feedstaging
select 1, 2, '<Datas>
<Data SiteID="2" TestGoupID="1" GroupName="Production">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
<Data SiteID="2" TestGoupID="2" GroupName="Beta">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
<Data SiteID="2" TestGoupID="3" GroupName="Control">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
</Datas>', '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000'



SELECT  data.query('<KPICounts>
{
for  $i in /Datas/Data
return
<KPICount SiteID="{$i/@SiteID}" testID = "{sql:column("testid")}" TestGoupID="{$i/@TestGoupID}" GroupName="{$i/@GroupName}" Name="{$i/dxp-aggregates/dxp-metric/@name}" DataType="{$i/dxp-aggregates/dxp-metric/@type}" Value="{$i/dxp-aggregates/dxp-metric/@value}" />
}
</KPICounts>') 
FROM @FeedStaging f
CROSS APPLY (SELECT TOP 1 testid
             FROM @test
             WHERE Startdate = f.startdate
             AND ENdDate=f.Enddate
             ORDER BY testid) t


output
----------------------------------------------
<KPICounts>
  <KPICount SiteID="2" testID="1" TestGoupID="1" GroupName="Production" Name="ga:goal12ConversionRate" DataType="percent" Value="0.0" />
  <KPICount SiteID="2" testID="1" TestGoupID="2" GroupName="Beta" Name="ga:goal12ConversionRate" DataType="percent" Value="0.0" />
  <KPICount SiteID="2" testID="1" TestGoupID="3" GroupName="Control" Name="ga:goal12ConversionRate" DataType="percent" Value="0.0" />
</KPICounts>

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

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-11 : 20:47:51

Thanks V for the quick answer.
I now need to join startdate, enddate, and siteid and get the top 1. I will like to get the beloow result. Thanks again.
I spent the whole day trying to join xml and a column and i still can't figure it out.

result:
<KPICounts>
<KPICount SiteID="2" testID="2" TestGoupID="1" GroupName="Production" Name="ga:goal12ConversionRate" DataType="percent" Value="0.0" />
<KPICount SiteID="2" testID="2" TestGoupID="2" GroupName="Beta" Name="ga:goal12ConversionRate" DataType="percent" Value="0.0" />
<KPICount SiteID="2" testID="2" TestGoupID="3" GroupName="Control" Name="ga:goal12ConversionRate" DataType="percent" Value="0.0" />
</KPICounts>

------------------------------------------------------------------------------

declare @test table (testid int, siteid int, startdate datetime, enddate datetime)
declare @feedstaging table (feedstagingid int, feedid int, data xml, startdate datetime, enddate datetime)

insert @test
select 1, 1, '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000' union all
select 2, 2, '2011-09-27 00:00:00.000', '2012-09-27 23:59:59.000' union all
select 3, 2, '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000'

insert @feedstaging
select 1, 2, '<Datas>
<Data SiteID="2" TestGoupID="1" GroupName="Production">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
<Data SiteID="2" TestGoupID="2" GroupName="Beta">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data><Data SiteID="2" TestGoupID="3" GroupName="Control">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
</Datas>', '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000'
union all
select 2, 1, '<Datas>
<Data SiteID="2" TestGoupID="1" GroupName="Production">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
<Data SiteID="2" TestGoupID="2" GroupName="Beta">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
<Data SiteID="2" TestGoupID="3" GroupName="Control">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
</Datas>', '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000'

select * from @Test
select * from @feedstaging

SELECT data.query('<KPICounts>
{
for $i in /Datas/Data
return
<KPICount SiteID="{$i/@SiteID}"
testID = "{sql:column("TestID")}"
TestGoupID="{$i/@TestGoupID}"
GroupName="{$i/@GroupName}"
Name="{$i/dxp-aggregates/dxp-metric/@name}"
DataType="{$i/dxp-aggregates/dxp-metric/@type}"
Value="{$i/dxp-aggregates/dxp-metric/@value}" />
}
</KPICounts>')
FROM @FeedStaging f
CROSS APPLY (SELECT TOP 1 testid, SiteID
FROM @test
WHERE Startdate = f.startdate
AND ENdDate=f.Enddate
ORDER BY testid) t
where feedid = 2 and startdate = '2011-09-27 00:00:00.000' and enddate = '2011-09-27 23:59:59.000'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 01:16:00

how did your requirement change? previous post showed testid as 1 now how it became 2? also i've joined to @test table using same logic you specified

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

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-12 : 01:21:41

The @test table has a siteid column and the @feedstaging table has also a siteid in the xml. I noticed that after I talked to my coworker.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 01:45:16

if thats the case, add the additional column also in the join apart from date fields.

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

basicconfiguration
Constraint Violating Yak Guru

358 Posts

Posted - 2011-10-12 : 02:04:35

Thanks V,
I did it.

this is the answer if anyone is interested.

declare @test table (testid int, siteid int, startdate datetime, enddate datetime)
declare @feedstaging table (feedstagingid int, feedid int, data xml, startdate datetime, enddate datetime)

insert @test
select 1, 1, '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000' union all
select 2, 2, '2011-09-27 00:00:00.000', '2012-09-27 23:59:59.000' union all
select 3, 2, '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000'

insert @feedstaging
select 1, 2, '<Datas>
<Data SiteID="2" TestGoupID="1" GroupName="Production">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
<Data SiteID="2" TestGoupID="2" GroupName="Beta">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data><Data SiteID="2" TestGoupID="3" GroupName="Control">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
</Datas>', '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000'
union all
select 2, 1, '<Datas>
<Data SiteID="2" TestGoupID="1" GroupName="Production">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
<Data SiteID="2" TestGoupID="2" GroupName="Beta">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
<Data SiteID="2" TestGoupID="3" GroupName="Control">
<dxp-aggregates>
<dxp-metric confidenceInterval="0.0" name="ga:goal12ConversionRate" type="percent" value="0.0" />
</dxp-aggregates>
</Data>
</Datas>', '2011-09-27 00:00:00.000', '2011-09-27 23:59:59.000'

select * from @Test
select * from @feedstaging

SELECT data.query('<KPICounts>
{
for $i in /Datas/Data
return
<KPICount SiteID="{$i/@SiteID}"
testID = "{sql:column("TestID")}"
TestGoupID="{$i/@TestGoupID}"
GroupName="{$i/@GroupName}"
Name="{$i/dxp-aggregates/dxp-metric/@name}"
DataType="{$i/dxp-aggregates/dxp-metric/@type}"
Value="{$i/dxp-aggregates/dxp-metric/@value}" />
}
</KPICounts>')
FROM @FeedStaging f
CROSS APPLY (SELECT TOP 1 testid
FROM @test
WHERE Startdate = f.startdate
AND ENdDate=f.Enddate
and siteid = f.data.value('(/Datas/Data/@SiteID)[1]', 'int')

ORDER BY testid) t
where feedid = 2 and startdate = '2011-09-27 00:00:00.000' and enddate = '2011-09-27 23:59:59.000'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 02:08:23

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources