USE Demo
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.[Employee_WS]') AND type in (N'U'))
BEGIN
CREATE TABLE dbo.Employee_WS(
[NationalIDNumber] [nvarchar](15) NOT NULL,
[LoginID] [nvarchar](256) NOT NULL,
[JobTitle] [nvarchar](50) NOT NULL,
[BirthDate] [date] NOT NULL,
[MaritalStatus] [nchar](1) NOT NULL,
[Gender] [nchar](1) NOT NULL,
[HireDate] [date] NOT NULL,
) ON [PRIMARY]
END
[WebMethod]
public XmlDocument GetEmployees()
{
var xml = "<?xml version=\"1.0\" encoding=\"utf-16\" ?>\n";
try
{
//string stConnectionOLEDB = @"Data Source=HSSSC1PCL01198\SQLSERVER2014;Initial Catalog=AdventureWorks2014;Provider=SQLNCLI11.0;Integrated Security=SSPI;";
string stConnectionOLEDB = @"Data Source=DUNGDT\SQLSERVER2014;Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;";
string stQueryText = "SELECT NationalIDNumber,[LoginID],JobTitle,BirthDate,[MaritalStatus],[Gender],[HireDate] FROM HumanResources.Employee WHERE NationalIDNumber In(695256908,134969118) FOR XML RAW('Employee'),ROOT('Employees'),ELEMENTS;";
using (SqlConnection sqlcon = new SqlConnection(stConnectionOLEDB))
{
sqlcon.Open();
SqlCommand cmd = new SqlCommand(stQueryText, sqlcon);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
xml = reader[0].ToString();
}
sqlcon.Close();
}
}
catch
{
}
XmlDocument xmldoc = new XmlDocument();
xmldoc.LoadXml(xml);
return xmldoc;
}
<?xml version="1.0"?>
<Employees xmlns="">
<Employee>
<NationalIDNumber>10708100</NationalIDNumber>
<LoginID>adventure-works\frank1</LoginID>
<JobTitle>Production Technician - WC50</JobTitle>
<BirthDate>1971-07-24</BirthDate>
<MaritalStatus>S</MaritalStatus>
<Gender>M</Gender>
<HireDate>2009-02-23</HireDate>
</Employee>
</Employees>
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented: