SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to over come this XML parsing ??		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Posting Yak Master

India
196 Posts
Posted - 02/13/2013 :  08:55:16  Show Profile  Reply with Quote
Hello all,

while i am executing this XML format i am getting Empty Columns with Headers....




 declare @MyXMLt xml =CONVERT(XML, N'<?xml version="1.0" encoding="utf-16"?>  <DataTable>    
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">      
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="PersonalDetails" msdata:UseCurrentLocale="true">        
<xs:complexType>          
<xs:choice minOccurs="0" maxOccurs="unbounded">            
<xs:element name="PersonalDetails">              
<xs:complexType>                
<xs:sequence>                 
 <xs:element name="Name" type="xs:string" minOccurs="0" />                 
  <xs:element name="MaritalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="Height" type="xs:string" minOccurs="0" />                  
  <xs:element name="Caste" type="xs:string" minOccurs="0" />                  
  <xs:element name="SubCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="PersonalValues" type="xs:string" minOccurs="0" />                  
  <xs:element name="Complexion" type="xs:string" minOccurs="0" />                  
  <xs:element name="BodyType" type="xs:string" minOccurs="0" />                  
  <xs:element name="PhysicalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="Citizenship" type="xs:string" minOccurs="0" />                  
  <xs:element name="BronCitizenship" type="xs:string" minOccurs="0" />                  
  <xs:element name="NoOfChildren" type="xs:string" minOccurs="0" />                  
  <xs:element name="ChildrenStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedName" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedMaritalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedHeight" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedSubCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedPersonalValues" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedComplexion" type="xs:string" minOccurs="0" />                 
   <xs:element name="IsReviewedBodyType" type="xs:string" minOccurs="0" />                  
   <xs:element name="IsReviewedPhysicalStatus" type="xs:string" minOccurs="0" />                 
    <xs:element name="IsReviewedCitizenship" type="xs:string" minOccurs="0" />                  
    <xs:element name="IsReviewedBronCitizenship" type="xs:string" minOccurs="0" />                
    </xs:sequence>              
    </xs:complexType>            
    </xs:element>          
    </xs:choice>        
    </xs:complexType>      
    </xs:element>    
    </xs:schema>    
    <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">      
    <DocumentElement>        
    <PersonalDetails diffgr:id="PersonalDetails1" msdata:rowOrder="0" diffgr:hasChanges="inserted">          
    <Name>alla Kishore</Name>          
    <MaritalStatus>43</MaritalStatus>          
      
    </PersonalDetails>      
    </DocumentElement>    
    </diffgr:diffgram>  
    </DataTable>',2)
       SELECT
 a.b.value('PersonalDetails[1]/Name[1]','varchar(50)') AS Name,
 a.b.value('PersonalDetails[1]/MaritalStatus[1]','varchar(10)') AS MaritalStatus,
 a.b.value('PersonalDetails[1]/Height[1]','varchar(10)') AS Height

 FROM @MyXMLt.nodes('DocumentElement') a(b)

Here in this below code i need to get result of this in table format.

 <DocumentElement>        
    <PersonalDetails diffgr:id="PersonalDetails1" msdata:rowOrder="0" diffgr:hasChanges="inserted">          
    <Name>alla Kishore</Name>          
    <MaritalStatus>43</MaritalStatus>          
      
    </PersonalDetails>      
    </DocumentElement>


Result of this one.

table is showing with empty data....

P.V.P.MOhan

James K
Flowing Fount of Yak Knowledge

1501 Posts
Posted - 02/13/2013 :  09:16:51  Show Profile  Reply with Quote
Either give the full xpath for DocumentElement or change it to this (to find it wherever it is in the tree)
FROM @MyXMLt.nodes('//DocumentElement') a(b)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/13/2013 :  11:45:20  Show Profile  Reply with Quote
should be



declare @MyXMLt xml =CONVERT(XML, N'<?xml version="1.0" encoding="utf-16"?>  <DataTable>    
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">      
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="PersonalDetails" msdata:UseCurrentLocale="true">        
<xs:complexType>          
<xs:choice minOccurs="0" maxOccurs="unbounded">            
<xs:element name="PersonalDetails">              
<xs:complexType>                
<xs:sequence>                 
 <xs:element name="Name" type="xs:string" minOccurs="0" />                 
  <xs:element name="MaritalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="Height" type="xs:string" minOccurs="0" />                  
  <xs:element name="Caste" type="xs:string" minOccurs="0" />                  
  <xs:element name="SubCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="PersonalValues" type="xs:string" minOccurs="0" />                  
  <xs:element name="Complexion" type="xs:string" minOccurs="0" />                  
  <xs:element name="BodyType" type="xs:string" minOccurs="0" />                  
  <xs:element name="PhysicalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="Citizenship" type="xs:string" minOccurs="0" />                  
  <xs:element name="BronCitizenship" type="xs:string" minOccurs="0" />                  
  <xs:element name="NoOfChildren" type="xs:string" minOccurs="0" />                  
  <xs:element name="ChildrenStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedName" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedMaritalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedHeight" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedSubCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedPersonalValues" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedComplexion" type="xs:string" minOccurs="0" />                 
   <xs:element name="IsReviewedBodyType" type="xs:string" minOccurs="0" />                  
   <xs:element name="IsReviewedPhysicalStatus" type="xs:string" minOccurs="0" />                 
    <xs:element name="IsReviewedCitizenship" type="xs:string" minOccurs="0" />                  
    <xs:element name="IsReviewedBronCitizenship" type="xs:string" minOccurs="0" />                
    </xs:sequence>              
    </xs:complexType>            
    </xs:element>          
    </xs:choice>        
    </xs:complexType>      
    </xs:element>    
    </xs:schema>    
    <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">      
    <DocumentElement>        
    <PersonalDetails diffgr:id="PersonalDetails1" msdata:rowOrder="0" diffgr:hasChanges="inserted">          
    <Name>alla Kishore</Name>          
    <MaritalStatus>43</MaritalStatus>          
      
    </PersonalDetails>      
    </DocumentElement>    
    </diffgr:diffgram>  
    </DataTable>',2)
    
    ;WITH XMLNAMESPACES('urn:schemas-microsoft-com:xml-diffgram-v1' as diffgr)
       SELECT
 a.b.value('PersonalDetails[1]/Name[1]','varchar(50)') AS Name,
 a.b.value('PersonalDetails[1]/MaritalStatus[1]','varchar(10)') AS MaritalStatus,
 a.b.value('PersonalDetails[1]/Height[1]','varchar(10)') AS Height

 FROM @MyXMLt.nodes('/DataTable/diffgr:diffgram/DocumentElement') a(b)


output
-----------------------
Name	        MaritalStatus	Height
alla Kishore	43	        NULL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Edited by - visakh16 on 02/13/2013 11:48:38
Go to Top of Page

mohan123
Posting Yak Master

India
196 Posts
Posted - 02/13/2013 :  23:58:41  Show Profile  Reply with Quote
hey visakh thanks a lot it worked fine....but now i am in middle of the solution again i will come up with another question...Suggest me

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/14/2013 :  00:53:08  Show Profile  Reply with Quote
welcome..
please fell free to post if you need further clarification

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

mohan123
Posting Yak Master

India
196 Posts
Posted - 02/14/2013 :  01:37:26  Show Profile  Reply with Quote
now i am having my requirement like this ??

there are 2 queries which can execute in sql and see the out put ??


declare @T table
(
  XMLCol xml
)

insert into @T values
('<Menu>
    <Id>1</Id>
      <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg</Url>
  
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-2.jpg</Url>
 
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-3.jpg</Url>
  
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-4.jpg</Url>
  
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-5.jpg</Url>
  </Menu>')

select X.N.value('Id[1]', 'int') as Id,
       X.N.value('Url[1]', 'varchar(max)') as Url
from @T as T
  cross apply T.XMLCol.nodes('/Menu') as X(N)



Id	Url
1	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg

It will give one kind of result

here is another query 


declare @T table
(
  XMLCol xml
)

insert into @T values
('<Menu>
    <Id>1</Id>
      <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg</Url>
  </Menu>
  <Menu>
    <Id>1</Id>
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-2.jpg</Url>
  </Menu>
  <Menu>
    <Id>1</Id>
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-3.jpg</Url>
  </Menu>
  <Menu>
    <Id>1</Id>
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-4.jpg</Url>
  </Menu>
  <Menu>
    <Id>1</Id>
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-5.jpg</Url>
  </Menu>')

select X.N.value('Id[1]', 'int') as Id,
       X.N.value('Url[1]', 'varchar(max)') as Url
from @T as T
  cross apply T.XMLCol.nodes('/Menu') as X(N)



Id	Url
1	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg
1	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-2.jpg
1	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-3.jpg
1	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-4.jpg
1	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-5.jpg


can observe for the 2 queries if we give

<Menu>
<Id>1</Id>
</Menu>
<Menu>
<Id>2</Id>
</Menu>
<Menu>
<Id>3</Id>
</Menu>
<Id>4</Id>
</Menu>
for every xml nodes it is giving 2 nd query output

if i am giving menu and ID at first and last
getting first result set...
how to get 2nd output for first query set
</Menu>
<Id></Id>
</Menu>

P.V.P.MOhan
Go to Top of Page

mohan123
Posting Yak Master

India
196 Posts
Posted - 02/14/2013 :  02:23:53  Show Profile  Reply with Quote
suggest me i am unable to move from here onwards

P.V.P.MOhan
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

1501 Posts
Posted - 02/14/2013 :  06:06:05  Show Profile  Reply with Quote
Shown below is one way. 
select X.N.value('../Id[1]', 'int') as Id,
       X.N.value('.', 'varchar(max)') as Url
from @T as T
  cross apply T.XMLCol.nodes('/Menu/Url') as X(N)
In this query, you are navigating down one more level in the cross apply, so you will pick up every Url node. Then to get the Id node, in the select portion, you are navigating up one node.
Go to Top of Page

mohan123
Posting Yak Master

India
196 Posts
Posted - 02/14/2013 :  23:42:32  Show Profile  Reply with Quote
yeah james this works fine for me...thanks...But this query can be written in Cross Apply with out CTE

please check 


declare @MyXMLt xml =CONVERT(XML, N'<?xml version="1.0" encoding="utf-16"?>  <DataTable>    
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">      
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="PersonalDetails" msdata:UseCurrentLocale="true">        
<xs:complexType>          
<xs:choice minOccurs="0" maxOccurs="unbounded">            
<xs:element name="PersonalDetails">              
<xs:complexType>                
<xs:sequence>                 
 <xs:element name="Name" type="xs:string" minOccurs="0" />                 
  <xs:element name="MaritalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="Height" type="xs:string" minOccurs="0" />                  
  <xs:element name="Caste" type="xs:string" minOccurs="0" />                  
  <xs:element name="SubCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="PersonalValues" type="xs:string" minOccurs="0" />                  
  <xs:element name="Complexion" type="xs:string" minOccurs="0" />                  
  <xs:element name="BodyType" type="xs:string" minOccurs="0" />                  
  <xs:element name="PhysicalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="Citizenship" type="xs:string" minOccurs="0" />                  
  <xs:element name="BronCitizenship" type="xs:string" minOccurs="0" />                  
  <xs:element name="NoOfChildren" type="xs:string" minOccurs="0" />                  
  <xs:element name="ChildrenStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedName" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedMaritalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedHeight" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedSubCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedPersonalValues" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedComplexion" type="xs:string" minOccurs="0" />                 
   <xs:element name="IsReviewedBodyType" type="xs:string" minOccurs="0" />                  
   <xs:element name="IsReviewedPhysicalStatus" type="xs:string" minOccurs="0" />                 
    <xs:element name="IsReviewedCitizenship" type="xs:string" minOccurs="0" />                  
    <xs:element name="IsReviewedBronCitizenship" type="xs:string" minOccurs="0" />                
    </xs:sequence>              
    </xs:complexType>            
    </xs:element>          
    </xs:choice>        
    </xs:complexType>      
    </xs:element>    
    </xs:schema>    
    <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">      
    <DocumentElement>        
    <PersonalDetails diffgr:id="PersonalDetails1" msdata:rowOrder="0" diffgr:hasChanges="inserted">          
    <Name>alla Kishore</Name>          
    <MaritalStatus>43</MaritalStatus>          
      
    </PersonalDetails>      
    </DocumentElement>    
    </diffgr:diffgram>  
    </DataTable>',2)
    
    ;WITH XMLNAMESPACES('urn:schemas-microsoft-com:xml-diffgram-v1' as diffgr)
       SELECT
 a.b.value('PersonalDetails[1]/Name[1]','varchar(50)') AS Name,
 a.b.value('PersonalDetails[1]/MaritalStatus[1]','varchar(10)') AS MaritalStatus,
 a.b.value('PersonalDetails[1]/Height[1]','varchar(10)') AS Height

 FROM @MyXMLt.nodes('/DataTable/diffgr:diffgram/DocumentElement') a(b)


P.V.P.MOhan
Edited by - mohan123 on 02/14/2013 23:43:58
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/14/2013 :  23:54:26  Show Profile  Reply with Quote
quote:
Originally posted by mohan123

yeah james this works fine for me...thanks...But this query can be written in Cross Apply with out CTE

please check 


declare @MyXMLt xml =CONVERT(XML, N'<?xml version="1.0" encoding="utf-16"?>  <DataTable>    
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">      
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="PersonalDetails" msdata:UseCurrentLocale="true">        
<xs:complexType>          
<xs:choice minOccurs="0" maxOccurs="unbounded">            
<xs:element name="PersonalDetails">              
<xs:complexType>                
<xs:sequence>                 
 <xs:element name="Name" type="xs:string" minOccurs="0" />                 
  <xs:element name="MaritalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="Height" type="xs:string" minOccurs="0" />                  
  <xs:element name="Caste" type="xs:string" minOccurs="0" />                  
  <xs:element name="SubCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="PersonalValues" type="xs:string" minOccurs="0" />                  
  <xs:element name="Complexion" type="xs:string" minOccurs="0" />                  
  <xs:element name="BodyType" type="xs:string" minOccurs="0" />                  
  <xs:element name="PhysicalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="Citizenship" type="xs:string" minOccurs="0" />                  
  <xs:element name="BronCitizenship" type="xs:string" minOccurs="0" />                  
  <xs:element name="NoOfChildren" type="xs:string" minOccurs="0" />                  
  <xs:element name="ChildrenStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedName" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedMaritalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedHeight" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedSubCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedPersonalValues" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedComplexion" type="xs:string" minOccurs="0" />                 
   <xs:element name="IsReviewedBodyType" type="xs:string" minOccurs="0" />                  
   <xs:element name="IsReviewedPhysicalStatus" type="xs:string" minOccurs="0" />                 
    <xs:element name="IsReviewedCitizenship" type="xs:string" minOccurs="0" />                  
    <xs:element name="IsReviewedBronCitizenship" type="xs:string" minOccurs="0" />                
    </xs:sequence>              
    </xs:complexType>            
    </xs:element>          
    </xs:choice>        
    </xs:complexType>      
    </xs:element>    
    </xs:schema>    
    <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">      
    <DocumentElement>        
    <PersonalDetails diffgr:id="PersonalDetails1" msdata:rowOrder="0" diffgr:hasChanges="inserted">          
    <Name>alla Kishore</Name>          
    <MaritalStatus>43</MaritalStatus>          
      
    </PersonalDetails>      
    </DocumentElement>    
    </diffgr:diffgram>  
    </DataTable>',2)
    
    ;WITH XMLNAMESPACES('urn:schemas-microsoft-com:xml-diffgram-v1' as diffgr)
       SELECT
 a.b.value('PersonalDetails[1]/Name[1]','varchar(50)') AS Name,
 a.b.value('PersonalDetails[1]/MaritalStatus[1]','varchar(10)') AS MaritalStatus,
 a.b.value('PersonalDetails[1]/Height[1]','varchar(10)') AS Height

 FROM @MyXMLt.nodes('/DataTable/diffgr:diffgram/DocumentElement') a(b)


P.V.P.MOhan

thats not a CTE its just defining the namespacing beforehand

if you want to dispense with it, use this instead


declare @MyXMLt xml =CONVERT(XML, N'<?xml version="1.0" encoding="utf-16"?>  <DataTable>    
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">      
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="PersonalDetails" msdata:UseCurrentLocale="true">        
<xs:complexType>          
<xs:choice minOccurs="0" maxOccurs="unbounded">            
<xs:element name="PersonalDetails">              
<xs:complexType>                
<xs:sequence>                 
 <xs:element name="Name" type="xs:string" minOccurs="0" />                 
  <xs:element name="MaritalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="Height" type="xs:string" minOccurs="0" />                  
  <xs:element name="Caste" type="xs:string" minOccurs="0" />                  
  <xs:element name="SubCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="PersonalValues" type="xs:string" minOccurs="0" />                  
  <xs:element name="Complexion" type="xs:string" minOccurs="0" />                  
  <xs:element name="BodyType" type="xs:string" minOccurs="0" />                  
  <xs:element name="PhysicalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="Citizenship" type="xs:string" minOccurs="0" />                  
  <xs:element name="BronCitizenship" type="xs:string" minOccurs="0" />                  
  <xs:element name="NoOfChildren" type="xs:string" minOccurs="0" />                  
  <xs:element name="ChildrenStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedName" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedMaritalStatus" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedHeight" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedSubCaste" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedPersonalValues" type="xs:string" minOccurs="0" />                  
  <xs:element name="IsReviewedComplexion" type="xs:string" minOccurs="0" />                 
   <xs:element name="IsReviewedBodyType" type="xs:string" minOccurs="0" />                  
   <xs:element name="IsReviewedPhysicalStatus" type="xs:string" minOccurs="0" />                 
    <xs:element name="IsReviewedCitizenship" type="xs:string" minOccurs="0" />                  
    <xs:element name="IsReviewedBronCitizenship" type="xs:string" minOccurs="0" />                
    </xs:sequence>              
    </xs:complexType>            
    </xs:element>          
    </xs:choice>        
    </xs:complexType>      
    </xs:element>    
    </xs:schema>    
    <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">      
    <DocumentElement>        
    <PersonalDetails diffgr:id="PersonalDetails1" msdata:rowOrder="0" diffgr:hasChanges="inserted">          
    <Name>alla Kishore</Name>          
    <MaritalStatus>43</MaritalStatus>          
      
    </PersonalDetails>      
    </DocumentElement>    
    </diffgr:diffgram>  
    </DataTable>',2)
    

       SELECT
 a.b.value('PersonalDetails[1]/Name[1]','varchar(50)') AS Name,
 a.b.value('PersonalDetails[1]/MaritalStatus[1]','varchar(10)') AS MaritalStatus,
 a.b.value('PersonalDetails[1]/Height[1]','varchar(10)') AS Height

 FROM @MyXMLt.nodes('declare namespace diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"; /DataTable/diffgr:diffgram/DocumentElement') a(b)


output
----------------------------------------
Name	        MaritalStatus	Height
----------------------------------------
alla Kishore	43	        NULL


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

mohan123
Posting Yak Master

India
196 Posts
Posted - 02/15/2013 :  00:09:35  Show Profile  Reply with Quote
Now i learnt the differnce between them.....Thanks visakh

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/15/2013 :  00:21:19  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

mohan123
Posting Yak Master

India
196 Posts
Posted - 02/15/2013 :  00:52:25  Show Profile  Reply with Quote
how we can get like this ??
Here i gave <XYZ>A</XYZ> <XYZ>B</XYZ>, <XYZ>C</XYZ> etc

declare @T table
(
  XMLCol xml
)

insert into @T values
('<Menu>
    <Id>1</Id>
     <XYZ>A</XYZ>
      <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg</Url>
  <XYZ>B</XYZ>
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-2.jpg</Url>
 <XYZ>C</XYZ>
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-3.jpg</Url>
  <XYZ>D</XYZ>
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-4.jpg</Url>
  <XYZ>E</XYZ>
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-5.jpg</Url>
  </Menu>')


select X.N.value('../Id[1]', 'int') as Id,
X.N.value('../XYZ[1]', 'varchar(max)') as XYZ,
       X.N.value('.', 'varchar(max)') as Url
from @T as T
  cross apply T.XMLCol.nodes('/Menu/Url') as X(N)




1	A	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg
1	A	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-2.jpg
1	A	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-3.jpg
1	A	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-4.jpg
1	A	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-5.jpg


My desired out put should be like this ....


1	A	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg
1	B	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-2.jpg
1	C	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-3.jpg
1	D	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-4.jpg
1	E	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-5.jpg


P.V.P.MOhan
Edited by - mohan123 on 02/15/2013 00:53:16
Go to Top of Page

mohan123
Posting Yak Master

India
196 Posts
Posted - 02/15/2013 :  01:25:15  Show Profile  Reply with Quote
Suggest me how we can do this one???

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/15/2013 :  02:36:59  Show Profile  Reply with Quote
the xml structure is not proper. there's no grouping node for corresponding XYZ and URL nodes

if you've such a node then its easy

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

mohan123
Posting Yak Master

India
196 Posts
Posted - 02/15/2013 :  05:12:38  Show Profile  Reply with Quote
with in this example how can we get please explain it in your way....


My desired out put should be like this ....



1	A	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg
1	B	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-2.jpg
1	C	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-3.jpg
1	D	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-4.jpg
1	E	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-5.jpg


P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 02/15/2013 :  05:48:57  Show Profile  Reply with Quote
quote:
Originally posted by mohan123

with in this example how can we get please explain it in your way....


My desired out put should be like this ....



1	A	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg
1	B	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-2.jpg
1	C	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-3.jpg
1	D	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-4.jpg
1	E	http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-5.jpg


P.V.P.MOhan

there's no direct way of correlating the URL nodes against corresponding XYZ ones. Thats why i told you need a higher level node to group them together. like this


<Menu>
    <Id>1</Id>
<Node>
     <XYZ>A</XYZ>
      <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg</Url>
</Node>
<Node>
  <XYZ>B</XYZ>
    <Url>http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-2.jpg</Url>
</Node>
.... 
 </Menu>


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000