Inconsistent, but there should be an explanation
-
This refers to the records returned by the "For XML auto" in ADO.Net. The first case is simply retirning results from a select statement from a table returning some columns with for xml auto. -------------------------------------------- [No erros, results returned] Database2, connection2
SqlConnection1.Open() Dim SQLCmd As New SqlClient.SqlCommand SQLCmd.Connection = SqlConnection1 SQLCmd.CommandType = CommandType.Text SQLCmd.CommandTimeout = 20 SQLCmd.CommandText = "SELECT CustomerID, CompanyName, City, PostalCode, Phone FROM Customers FOR xml auto" Dim myXmlReader As System.Xml.XmlReader = SQLCmd.ExecuteXmlReader() 'myXmlReader.MoveToContent() 'myXmlReader.MoveToElement() Response.Write("") While myXmlReader.Read 'Response.Write(myXmlReader.Item("CustomerID") & " ") Response.Write("") End While Response.Write(" " & myXmlReader.Item("CustomerID") & " " & myXmlReader.Item("CompanyName") & _ myXmlReader.Item("City") & "" & myXmlReader.Item("PostalCode") & " ") myXmlReader.Close() SqlConnection1.Close()[No errors, results returned] ----------------------- The second case is, results expected to be returned from table joins using For xml auto. In this case no results were returned, no errors or exceptions, exactly same trace info as in the first case [No errors, no output(empty table), trace info exactly same as the one with results returned] Database1, connection string1SqlConnection1.Open() Dim SQLCmd As New SqlClient.SqlCommand SQLCmd.Connection = SqlConnection1 SQLCmd.CommandType = CommandType.Text SQLCmd.CommandTimeout = 20 SQLCmd.CommandText = "SELECT nums.NumId, nums.Nums, color.colr FROM color INNER JOIN nums ON color.numid = nums.NumId FOR xml auto, ELEMENTS" 'SELECT nums.NumId, nums.Nums, color.colr FROM color INNER JOIN nums ON color.numid = nums.NumId FOR xml auto 'SELECT CustomerID, CompanyName, City, PostalCode, Phone FROM Customers FOR xml auto Dim myXmlReader As System.Xml.XmlReader = SQLCmd.ExecuteXmlReader() Response.Write("") While myXmlReader.Read 'Response.Write(myXmlReader.Item("CustomerID") & " ") Response.Write(" " & myXmlReader.Item("nums.NumId") & " " & myXmlReader.Item("nums.Nums") & _ m -
This refers to the records returned by the "For XML auto" in ADO.Net. The first case is simply retirning results from a select statement from a table returning some columns with for xml auto. -------------------------------------------- [No erros, results returned] Database2, connection2
SqlConnection1.Open() Dim SQLCmd As New SqlClient.SqlCommand SQLCmd.Connection = SqlConnection1 SQLCmd.CommandType = CommandType.Text SQLCmd.CommandTimeout = 20 SQLCmd.CommandText = "SELECT CustomerID, CompanyName, City, PostalCode, Phone FROM Customers FOR xml auto" Dim myXmlReader As System.Xml.XmlReader = SQLCmd.ExecuteXmlReader() 'myXmlReader.MoveToContent() 'myXmlReader.MoveToElement() Response.Write("") While myXmlReader.Read 'Response.Write(myXmlReader.Item("CustomerID") & " ") Response.Write("") End While Response.Write(" " & myXmlReader.Item("CustomerID") & " " & myXmlReader.Item("CompanyName") & _ myXmlReader.Item("City") & "" & myXmlReader.Item("PostalCode") & " ") myXmlReader.Close() SqlConnection1.Close()[No errors, results returned] ----------------------- The second case is, results expected to be returned from table joins using For xml auto. In this case no results were returned, no errors or exceptions, exactly same trace info as in the first case [No errors, no output(empty table), trace info exactly same as the one with results returned] Database1, connection string1SqlConnection1.Open() Dim SQLCmd As New SqlClient.SqlCommand SQLCmd.Connection = SqlConnection1 SQLCmd.CommandType = CommandType.Text SQLCmd.CommandTimeout = 20 SQLCmd.CommandText = "SELECT nums.NumId, nums.Nums, color.colr FROM color INNER JOIN nums ON color.numid = nums.NumId FOR xml auto, ELEMENTS" 'SELECT nums.NumId, nums.Nums, color.colr FROM color INNER JOIN nums ON color.numid = nums.NumId FOR xml auto 'SELECT CustomerID, CompanyName, City, PostalCode, Phone FROM Customers FOR xml auto Dim myXmlReader As System.Xml.XmlReader = SQLCmd.ExecuteXmlReader() Response.Write("") While myXmlReader.Read 'Response.Write(myXmlReader.Item("CustomerID") & " ") Response.Write(" " & myXmlReader.Item("nums.NumId") & " " & myXmlReader.Item("nums.Nums") & _ mPossibly the join yeilds no matches. Try the query in Query analyzer (first as a normal query, - no forXml, then just without the ELEMENTS directive). Why would anyone waste time arguing with an accountant about anything? Their sole function is to record what happenned, and any higher aspirations are mere delusions of grandeur. On the ladder of productive contributions they are the little rubber pads at the bottom that keep the thing from sliding out from under you. - Roger Wright
-
Possibly the join yeilds no matches. Try the query in Query analyzer (first as a normal query, - no forXml, then just without the ELEMENTS directive). Why would anyone waste time arguing with an accountant about anything? Their sole function is to record what happenned, and any higher aspirations are mere delusions of grandeur. On the ladder of productive contributions they are the little rubber pads at the bottom that keep the thing from sliding out from under you. - Roger Wright