Click here to Skip to main content
11,645,087 members (65,643 online)
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 700
1 jyo.net 494
2 Sergey Alexandrovich Kryukov 449
3 Afzaal Ahmad Zeeshan 444
4 CPallini 386
0 OriginalGriff 1,065
1 Sergey Alexandrovich Kryukov 683
2 DamithSL 681
3 Afzaal Ahmad Zeeshan 662
4 CPallini 555


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

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100