Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to over come this XML parsing ??

Author  Topic 

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-13 : 08:55:16
Hello all,

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



[CODE]
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)

[/CODE]
Here in this below code i need to get result of this in table format.
[CODE]
<DocumentElement>
<PersonalDetails diffgr:id="PersonalDetails1" msdata:rowOrder="0" diffgr:hasChanges="inserted">
<Name>alla Kishore</Name>
<MaritalStatus>43</MaritalStatus>

</PersonalDetails>
</DocumentElement>
[/CODE]

Result of this one.

table is showing with empty data....

P.V.P.MOhan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-13 : 09:16:51
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

52326 Posts

Posted - 2013-02-13 : 11:45:20
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/

Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-13 : 23:58:41
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

52326 Posts

Posted - 2013-02-14 : 00:53:08
welcome..
please fell free to post if you need further clarification

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-14 : 01:37:26
now i am having my requirement like this ??

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

[CODE]
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)
[/CODE]
[CODE]
Id Url
1 http://www.zmtcdn.com/menus/1211/menu-photo-for-barbeque-nation-1.jpg
[/CODE]
It will give one kind of result

here is another query

[CODE]
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)
[/CODE]
[CODE]
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
[/CODE]

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
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-14 : 02:23:53
suggest me i am unable to move from here onwards

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-14 : 06:06:05
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
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-14 : 23:42:32
yeah james this works fine for me...thanks...But this query can be written in Cross Apply with out CTE

please check

[CODE]
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)

[/CODE]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-14 : 23:54:26
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

[CODE]
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)

[/CODE]

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
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-15 : 00:09:35
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

52326 Posts

Posted - 2013-02-15 : 00:21:19
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-15 : 00:52:25
how we can get like this ??
Here i gave <XYZ>A</XYZ> <XYZ>B</XYZ>, <XYZ>C</XYZ> etc
[CODE]
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)
[/CODE]

[CODE]
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
[/CODE]

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

[CODE]
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
[/CODE]

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

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-15 : 01:25:15
Suggest me how we can do this one???

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 02:36:59
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
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-15 : 05:12:38
with in this example how can we get please explain it in your way....

[CODE]
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


[/CODE]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-15 : 05:48:57
quote:
Originally posted by mohan123

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

[CODE]
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


[/CODE]

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
   

- Advertisement -