Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Can someone algorithmically explain this TSQL UDF with XML?

Posted on 2012-04-11
1
Medium Priority
?
468 Views
Last Modified: 2012-08-14
Can someone please provide an algorithmic description of what this TSQL UDF is doing? I am not familiar with XML for TSQL.

FUNCTION [Common].[XmlArrayOfInt]

(@Xml AS XML (Common.ArrayOfIntegersSchema))

RETURNS @Result TABLE (Value INT NOT NULL)

AS

BEGIN
      INSERT INTO @Result(Value)
      SELECT T.c.value('.','int') AS Value
      FROM @Xml.nodes('/ArrayOfInt/int') AS T(c)
      RETURN
END

Also are value and nodes built-in TSQL functions? And what is '/ArrayOfInt/int'?

Thanks!
0
Comment
Question by:pae2
1 Comment
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 2000 total points
ID: 37834841
To start at the end: '/ArrayOfInt/int' is an xpath query with which you can select parts of an xml document. This one just selects all 'int' nodes under the root node 'ArrayOfInt' but it can contain a lot more complicated queries in itself.

Given an xml document as input, this function returns a table that contains all values in the 'int' nodes.

So given this xml document:

<ArrayOfInt>
  <int>6</int>
  <int>12</int>
  <int>7</int>
</ArrayOfInt>

Open in new window


It would return:

Value
-----
    6
   12
    7

Open in new window


Yes, @Xml.nodes and T.c.value are built-in functions to work with xml data. See for more info under the heading 'Methods on XML Data Type' at this page: http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx#sql2k5xml_topic3
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question