March 27, 2008 at 12:35 pm
Matt Miller (3/27/2008)
Christopher Ford (3/27/2008)
According to the link:SUM(OrderQty) OVER(PARTITION BY SalesOrderID)
That works fine...
Why can't I remember what it was about that, that would be really nice to have...Several people have talked about it, especially when it comes to ordered updates...
What did you mean Matt! you're not making sense today! =)
Hehe...Put the caffeine down Chris...:) Wow!
We're agreeing. PARTITION BY works fine...Mike C meant he wanted the ORDER BY to work in the OVER clause as well (for running totals) as part of his "wish list" of new features to have. He typoed it and said PARTITION BY (so I pointed it out to him, and he corrected himself), and thus this convoluted thread.
As of now the SUM() OVER(PARTITION BY) is a way to get grouped totals without a GROUP BY clause. The SQL (92?) standard mentioned using this OVER notation to implement "running" aggregates as well, but that hasn't materialized yet (though there are active rumors that this might make it into 2008).
Just to clarify for Chris - when you use the OVER clause with the ranking functions (RANK, ROW_NUMBER, etc.) you can use both the ORDER BY and PARTITION clauses. When you use OVER with an aggregate function (SUM, AVG, etc.) you can only use the PARTITION BY clause. If SQL Server implements the PARTITION BY and ORDER BY clauses for the OVER clause in aggregate functions, you can do some pretty handy running sum type calculations without cursors or a bunch of inner joins. I'd like to see the ORDER BY clause option added to the aggregate function OVER clause. This functionality is already available on other DBMS platforms like Oracle and DB2.
I believe the OVER clause for aggregate functions was introduced either in SQL:1999 or SQL:2003, but not sure which.
BTW - it doesn't appear to be in CTP 6 of 2008. Maybe it will be implemented around SP 1 time? 🙂
December 14, 2008 at 12:23 pm
This example really spelled out what I should do, and the discussions showed other ways of achieving the same thing.
I find the documentation in BOL sufficient but the lack of examples showing all of the option is what is really hindering my looking forward to working with XML in T-SQL.
March 18, 2009 at 7:18 am
Great post.
I have a question about parent nodes.
This is my Xml.
<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<PropertyExport Version="1.2">
<Property Id="85" DescId="2088">
<PhoneList>
<Phone PhoneTypeId="48" PhoneTypeString="Booking">
<AreaCode />
<CountryCode>45</CountryCode>
<Number>70121700</Number>
</Phone>
<Phone PhoneTypeId="51" PhoneTypeString="FaxBooking">
<AreaCode />
<CountryCode>45</CountryCode>
<Number>33239686</Number>
</Phone>
<Phone PhoneTypeId="52" PhoneTypeString="Reception">
<AreaCode />
<CountryCode>45</CountryCode>
<Number>33314801</Number>
</Phone>
</PhoneList>
</Property>
</PropertyExport>
I can find all the <Number> and <CountryCode> nodes, like this:
SELECT
T.c.value('(CountryCode/text())[1]', 'varchar(256)') as CountryCode,
T.c.value('(Number/text())[1]', 'varchar(256)') as PhoneNumber
FROM @x.nodes('//Property/PhoneList/Phone') T(c)
But i also want to get the id attribute from the <Property> node in the same select statement. How is that done?
SELECT
T.c.value('parentnode/parentnode/parentnode/@Id', 'int') as PropertyId,
T.c.value('(CountryCode/text())[1]', 'varchar(256)') as CountryCode,
T.c.value('(Number/text())[1]', 'varchar(256)') as PhoneNumber
FROM @x.nodes('//Property/PhoneList/Phone') T(c)
March 18, 2009 at 7:32 am
I found the solution.
Sometimes it helps, when you write your problem down on 'paper'.
SELECT
T.c.value('../../@Id[1]','int') AS PropertyId,
T.c.value('(CountryCode/text())[1]', 'varchar(256)') as CountryCode,
T.c.value('(Number/text())[1]', 'varchar(256)') as PhoneNumber
FROM @x.nodes('//Property/PhoneList/Phone') T(c)
March 18, 2009 at 12:30 pm
peter larsen (3/18/2009)
I found the solution.Sometimes it helps, when you write your problem down on 'paper'.
SELECT
T.c.value('../../@Id[1]','int') AS PropertyId,
T.c.value('(CountryCode/text())[1]', 'varchar(256)') as CountryCode,
T.c.value('(Number/text())[1]', 'varchar(256)') as PhoneNumber
FROM @x.nodes('//Property/PhoneList/Phone') T(c)
One caveat is that the ".." parent node axis step can hurt performance. If you think about it, the XML processor has to back up to find the parent node, which can be time consuming depending on how complex your XML is. It might be more efficient to use a CROSS APPLY to extract child nodes from the //Property nodes. Then again, if your XML data is small in size the performance may not be a factor anyway.
March 18, 2009 at 12:37 pm
...and this example shows how to achieve it with a CROSS APPLY.
SELECT
p.value('@Id[1]','int') AS PropertyId,
c.value('(CountryCode/text())[1]', 'varchar(256)') as CountryCode,
c.value('(Number/text())[1]', 'varchar(256)') as PhoneNumber
FROM @x.nodes('//Property') n(p)
CROSS APPLY p.nodes('PhoneList/Phone') t(c)
.
April 20, 2009 at 10:37 pm
I was looking for an example how to use sql:variable and iterate through it with an indexer.. I never found one and I thank this for giving me the idea. This is my code how i iterated through an xml doc
DECLARE @i TINYINT
SET @i = 0
WHILE @i < @NumberOfBatchFiles
BEGIN
SET @i = @i + 1
SELECT
x.value('Table[1]', 'NVARCHAR(20)') AS TableName,
x.value('File[1]', 'NVARCHAR(100)') AS BatchFile,
x.value('Fields[1]', 'NVARCHAR(50)') AS Fields
FROM
@Xml.nodes('/BatchControlFile/ImportList/ImportFile[position()=sql:variable("@i")]') e(x)
END
Thanks and more power to this site
October 6, 2009 at 12:52 pm
Hello All,
I wonder if anyone can help. I am passing xml to a stored procedure, and would like to iterate through it and save values to few different tables. I need to be able to detect the element name, and its attributes. I am including an xml string that will be passed to a stored procedure. Your help is greatly appreciated.....
<topic id="1" soundon="true" language="true">
<page id="1">
<zone id="1" x="50" y="185" width="540" height="600">
<textbox id="1">text</textbox>
</zone>
<zone id="2" x="620" y="90" width="430" height="765">
<graphic x="620" y="90" width="430" height="765" linCol="0x314d5b" fillCol="0x314d5b"></graphic>
<main_image alpha="0.2" x="620" y="90" width="645" height="645">http://www.learninglink12.edcomm.com/CMS/assets/images/right_legsondesk.jpg</main_image>
<textbox id="1" x="20" y="90" width="320">text</textbox>
</zone>
</page>
<page id="2">
<zone id="1" x="50" y="185" width="540" height="600">
<textbox id="1">text</textbox>
</zone>
<zone id="2" x="620" y="90" width="430" height="765">
<main_image alpha="0.2" x="620" y="90" width="645" height="645">http://www.learninglink12.edcomm.com/CMS/assets/images/right_meeting3.jpg</main_image>
</zone>
</page>
</topic>
October 7, 2009 at 3:35 pm
October 8, 2009 at 9:50 am
Thanks a lot! I will definitely try that.
June 8, 2010 at 3:06 pm
position()=sql:variable("@i") is not working for me, I am working on SQL 2005, basically I was trying to loop each node and insert an element..so if I use [1] or [2] its inserting in either first or second node, that's why I wanted to use while loop...in that I using the index as [position()=sql:variable("@i")] as well as [sql:variable("@i")] both are not working...please help..
June 8, 2010 at 9:03 pm
Please post a small repro script that shows your problem.
.
June 9, 2010 at 7:41 am
DECLARE @index int
SET @index = 0
DECLARE @charCount as varchar(1)
DECLARE @count int
SET @charcount = CAST(@CustomerData.query('count(/customer/customer)') as varchar)
SET @Count = CAST(@charCount as int)
WHILE @index < @count
BEGIN
DECLARE @ID2 uniqueidentifier
SET @ID2 = NewID()
SET @CustomerData.modify('insert element ID {"x"} before
(/customer/customer/firstName)[sql:variable("@index")]')
SET @CustomerData.modify('replace value of (/customer/customer/ID/text())[sql:variable("@index")] with sql:variable("@ID2")')
END
Instead of sql:variable("@index") if I use 1 or 2 its working..my question how to get the parameterized index
June 9, 2010 at 7:57 am
The syntax is not correct. The correct expression is [position()=sql:variable("@var")]. Here is an example:
DECLARE @x XML, @cnt INT
SELECT @x = '
<a>
<n>node 1</n>
<n>node 2</n>
<n>node 3</n>
</a>'
SELECT @cnt = 1
WHILE @cnt <= 3 BEGIN
SELECT @x.value('(/a/n[position()=sql:variable("@cnt")])[1]','VARCHAR(20)')
SELECT @cnt = @cnt + 1
END
/*
Prints
node 1
node 2
node 3
*/
.
June 9, 2010 at 9:14 am
Jocb, This is not working with SET statement, Its always inserting an element under first node.
could you please convert my example and send it back...Thanks for your help
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply
