SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XML Workshop XVII - Writing a LOOP to process XML elements in TSQL


XML Workshop XVII - Writing a LOOP to process XML elements in TSQL

Author
Message
Mike C
Mike C
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2803 Visits: 1168
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...Smile 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? Smile
Adam Seniuk
Adam Seniuk
SSChasing Mays
SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)SSChasing Mays (640 reputation)

Group: General Forum Members
Points: 640 Visits: 1040
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 12yrs in IT and 10yrs happily stuck with SQL.

http://aseniuk.wordpress.com
- SQL 2008/R2/2012/2014
- Oracle 8/9/10/11
- MySQL 4/5
peter larsen-490879
peter larsen-490879
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 18
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)
peter larsen-490879
peter larsen-490879
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 18
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)
Mike C
Mike C
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2803 Visits: 1168
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.
jacob sebastian
jacob sebastian
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 2523
...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)



.
en.aldemita
en.aldemita
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
pavlo.dmytrenko
pavlo.dmytrenko
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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>


jacob sebastian
jacob sebastian
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 2523
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.

.
pavlo.dmytrenko
pavlo.dmytrenko
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 12
Thanks a lot! I will definitely try that.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search