?
Solved

Can someone algorithmically explain this TSQL UDF with XML?

Posted on 2012-04-11
1
Medium Priority
?
477 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

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

588 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