February 22, 2010 at 8:32 am
GSquared (2/22/2010)
...My XQuery education has all been "trial and error", based on abysmal documentation and samples online and in BOL...
Me too - I still struggle with it to be honest.
I don't write much XMLy-type stuff, which I suppose doesn't help matters.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 27, 2012 at 11:39 pm
plz help me for for this topic..... how to write sql query in xml file....... is it possible......
February 28, 2012 at 5:59 am
What exactly are you trying to do. Please provide a detailed description.
February 28, 2012 at 6:32 am
I have to admit that I don’t understand why both queries use the same path in nodes method, but different path in value method. I would have thought that if the nodes method gets the same path as input, then the value method should also have the same path as input.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 20, 2012 at 2:40 pm
Please help me get the below result using the given xml:
DECLARE @XML XML
SET @XML = '<baseEmployeeDepartmentReln>
<relationships>
<Employee>
<empId>1</empId>
<empName>Ramu</empName>
</Employee>
<Department>
<deptId>1</deptId>
<deptName>Electrical</deptName>
</Department>
</relationships>
<relationships>
<Employee>
<empId>2</empId>
<empName>Ramana</empName>
</Employee>
<Department>
<deptId>1</deptId>
<deptName>Electrical</deptName>
</Department>
</relationships>
<relationships>
<Employee>
<empId>3</empId>
<empName>Raju</empName>
</Employee>
<Department>
<deptId>2</deptId>
<deptName>InformationTechnology</deptName>
</Department>
</relationships>
</baseEmployeeDepartmentReln>'
Output:
empIdempName deptIddeptName
--------------------------------------
1Ramu 1Electrical
2Ramana 1Electrical
3Raju 2InformationTechnology
July 24, 2012 at 8:04 am
DECLARE @XML XML
SET @XML = N'<baseEmployeeDepartmentReln>
<relationships>
<Employee>
<empId>1</empId>
<empName>Ramu</empName>
</Employee>
<Department>
<deptId>1</deptId>
<deptName>Electrical</deptName>
</Department>
</relationships>
<relationships>
<Employee>
<empId>2</empId>
<empName>Ramana</empName>
</Employee>
<Department>
<deptId>1</deptId>
<deptName>Electrical</deptName>
</Department>
</relationships>
<relationships>
<Employee>
<empId>3</empId>
<empName>Raju</empName>
</Employee>
<Department>
<deptId>2</deptId>
<deptName>InformationTechnology</deptName>
</Department>
</relationships>
</baseEmployeeDepartmentReln>'
SELECT
DV.Employee_Id
,DV.Employee_Name
, DV.Department_Id
, DV.Department_Name
FROM @XML.nodes('./baseEmployeeDepartmentReln/relationships')
AS T(relationships)
CROSS
APPLY (
SELECT
relationships.value('(Employee/empId/text())[1]', 'VARCHAR(100)')
, relationships.value('(Employee/empName/text())[1]', 'VARCHAR(100)')
, relationships.value('(Department/deptId/text())[1]', 'VARCHAR(100)')
, relationships.value('(Department/deptName/text())[1]', 'VARCHAR(100)')
)
AS DV (
Employee_Id
, Employee_Name
, Department_Id
, Department_Name
);
July 24, 2012 at 8:29 am
July 31, 2012 at 12:54 pm
Hi,
If I would like to put the above xml in below xml format, how to get the same result. Please let me know.
DECLARE @XML XML
SET @XML = N'<baseEmployeeDepartmentReln>
<relationships>
<Department>
<deptId>1</deptId>
<deptName>Electrical</deptName>
</Department>
<Emp>
<Employee>
<empId>1</empId>
<empName>Ramu</empName>
</Employee>
<Employee>
<empId>2</empId>
<empName>Ramana</empName>
</Employee>
</Emp>
</relationships>
<relationships>
<Emp>
<Employee>
<empId>3</empId>
<empName>Raju</empName>
</Employee>
</Emp>
<Department>
<deptId>2</deptId>
<deptName>InformationTechnology</deptName>
</Department>
</relationships>
</baseEmployeeDepartmentReln>'
deptIddeptNameempIdempName
---------------------------------------------------------
1Electrical1Ramu
1Electrical2Ramana
2InformationTechnology3Raju
---------------------------------------------------------
Thanks and Regards,
Narasimha Murthy
July 31, 2012 at 3:48 pm
DECLARE @XML XML
SET @XML = N'<userList>
<createdById>V353537</createdById>
<user>
<userId>NC85420</userId>
<roles>
<roleId>1</roleId>
</roles>
</user>
<user>
<userId>GY72272</userId>
<roles>
<roleId>2</roleId>
</roles>
<roles>
<roleId>3</roleId>
</roles>
</user>
</userList>'
Please write a query to parase the above xml to get the below resultset.
-------------------------------
createdByIduserIdroleId
-------------------------------
V353537NC854201
V353537GY722722
V353537GY722723
-------------------------------
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply