Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server ADO.NET
Can any one Please Explain about this code:
 
CREATE PROCEDURE SaveXML_Person(@XML XML)
AS
BEGIN
 
DECLARE @XML XML = 
'
<Person> 
	<Id>6</Id> 
	<Id>64</Id> 
	<Name>AABBc</Name> 
	<Family>AABBc</Family> 
	<IDNumber>120000</IDNumber> 
</Person>
'
 
DECLARE @TEMP TABLE (Id	int,Name nvarchar(50),Family nvarchar(50),IDNumber	int)
 
INSERT @TEMP
SELECT T.Item.value('Id[1]'			, 'varchar(20)'),
       T.Item.value('Name[1]'		, 'varchar(20)'),
       T.Item.value('Family[1]'		, 'varchar(20)'),
       T.Item.value('IDNumber[1]'	, 'varchar(20)')
FROM   Person CROSS APPLY X.nodes('/Person') AS T(Item)
 
END
Posted 17-Feb-13 9:12am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

The best described in MSDN documentation below
 
The nodes() method is useful when you want to shred an xml data type instance into relational data. It allows you to identify nodes that will be mapped into a new row.
Every xml data type instance has an implicitly provided context node. For the XML instance stored in a column or variable, this is the document node. The document node is the implicit node at the top of every xml data type instance.
The result of the nodes() method is a rowset that contains logical copies of the original XML instances. In these logical copies, the context node of every row instance is set to one of the nodes identified with the query expression, so that subsequent queries can navigate relative to these context nodes.
You can retrieve multiple values from the rowset. For example, you can apply the value() method to the rowset returned by nodes() and retrieve multiple values from the original XML instance. Note that the value() method, when applied to the XML instance, returns only one value.
 

http://msdn.microsoft.com/en-us/library/ms188282.aspx[^]
 
On a link you will find number of demos and examples on this topic.
 
UPD
on a example below
 
DECLARE @TEMP TABLE (Id int,Name nvarchar(50),Family nvarchar(50),IDNumber  int)
 
INSERT @TEMP
SELECT T.Item.value('Id[1]'         , 'varchar(20)'),
       T.Item.value('Name[1]'       , 'varchar(20)'),
       T.Item.value('Family[1]'     , 'varchar(20)'),
       T.Item.value('IDNumber[1]'   , 'varchar(20)')
FROM   Person CROSS APPLY X.nodes('/Person') AS T(Item)
 
procedure actually does nothing.
It creates temporary table (ID, Name, Family, IDNumber) and populates with values 6, AABBc, AABBc, 120000 number of times.
 
Number of times is equal to number of columns in Person table
  Permalink  
v2
Comments
behno0o0d at 18-Feb-13 0:55am
   
I have read that but honestly speaking, I did not understand:)
 
Could you please explain wit hsome simpler examples?
 
Thanks
Vyacheslav Voronenko at 18-Feb-13 2:19am
   
I think MSDN contains simplest. Have you tried any in SQL Manager?
 
For the first example on MSDN ...
 
Remarks
As an example, assume that you have the following table:
T (ProductModelID int, Instructions xml)
 
at what moment you have you lost the point?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
Check the following link...
 
If you know about XML then the article will explain you about "APPLY" Operator.
 
SQL Server APPLY Basics[^]
 
SQL Server CROSS APPLY and OUTER APPLY[^]
 

Regards,
GVPrabu
  Permalink  
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 454
1 Maciej Los 404
2 CPallini 185
3 Sergey Alexandrovich Kryukov 182
4 Richard MacCutchan 120
0 OriginalGriff 5,634
1 Sergey Alexandrovich Kryukov 4,763
2 Peter Leow 2,954
3 Maciej Los 2,614
4 DamithSL 2,465


Advertise | Privacy | Mobile
Web03 | 2.8.140718.1 | Last Updated 18 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid