Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

XML Workshop XVII - Writing a LOOP to process XML elements in TSQL Expand / Collapse
Author
Message
Posted Thursday, March 27, 2008 12:35 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:20 PM
Points: 1,276, Visits: 1,134
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? :)
Post #475661
Posted Sunday, December 14, 2008 12:23 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:56 AM
Points: 533, Visits: 868
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.




Over 11yrs in IT and 9yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012
- Oracle 8/9/10
- MySQL 4/5
Post #619328
Posted Wednesday, March 18, 2009 7:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:33 AM
Points: 6, Visits: 17
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)

Post #678409
Posted Wednesday, March 18, 2009 7:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:33 AM
Points: 6, Visits: 17
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)
Post #678432
Posted Wednesday, March 18, 2009 12:30 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 5:20 PM
Points: 1,276, Visits: 1,134
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.
Post #678794
Posted Wednesday, March 18, 2009 12:37 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
...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)



.
Post #678799
Posted Monday, April 20, 2009 10:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 7, 2011 12:44 AM
Points: 1, Visits: 16
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
Post #701159
Posted Tuesday, October 6, 2009 12:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:08 AM
Points: 2, Visits: 12
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>

Post #798728
Posted Wednesday, October 7, 2009 3:35 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
I would recommend reading this article that demonstrates how to iterate over the elements of an XML document. http://www.sqlservercentral.com/articles/XML/62290/

The XQuery function "local-name()" can be used to find out the name of an element or attribute.


.
Post #799571
Posted Thursday, October 8, 2009 9:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:08 AM
Points: 2, Visits: 12
Thanks a lot! I will definitely try that.
Post #800137
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse