FOR XML AUTO, ELEMENTS

I am using SQL Server 2000.
When I run SQL in Query Analyzer similar to the following, each row in the
results is truncated to 256 characters:
SELECT * FROM tblName
FOR XML AUTO, ELEMENTS
How can I prevent the output from truncating each row?
Also, is it possible for the output to be formatted with a CRLF after each
element, and appropriate indentation of elements?
Thanks
Bill
First, you can increase the limit of the result to 4000 characters. That way
you will see everything. However, since the query analyzer does not really
understand the XML, you should not attempt to use the XML there except for
doing visual checks. If you want to get the XML in a stream, use either the
ADO/ADO.Net mechanisms to get the XML stream back or use the SQLXML ISAPI.
In the later case, you would access the data through IE, and thus you would
get your pretty-printing of the XML.
Best regards
Michael
"bill" <belgie@.datamti.com> wrote in message
news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>I am using SQL Server 2000.
> When I run SQL in Query Analyzer similar to the following, each row in the
> results is truncated to 256 characters:
> SELECT * FROM tblName
> FOR XML AUTO, ELEMENTS
> How can I prevent the output from truncating each row?
> Also, is it possible for the output to be formatted with a CRLF after each
> element, and appropriate indentation of elements?
> Thanks
> Bill
>
|||I always change my settings to display 8192 instead of the annoying 256
standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
> First, you can increase the limit of the result to 4000 characters. That
> way you will see everything. However, since the query analyzer does not
> really understand the XML, you should not attempt to use the XML there
> except for doing visual checks. If you want to get the XML in a stream,
> use either the ADO/ADO.Net mechanisms to get the XML stream back or use
> the SQLXML ISAPI. In the later case, you would access the data through IE,
> and thus you would get your pretty-printing of the XML.
> Best regards
> Michael
> "bill" <belgie@.datamti.com> wrote in message
> news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>
|||Too much is not a problem. The reason why I said 4000 is that each stream
block (ie a row chunk) that is being returned is around 2034 bytes, so 4000
is enough).
But regardless of the setting, if you get the XML in more than one chunk in
the query analyzer, you will have to do some postprocessing to get rid of
the newlines.
Best regards
Mcihael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I always change my settings to display 8192 instead of the annoying 256
>standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
>
|||Yes a bit annoying that you have to do that yourself instead of checking
that "Don't give me irrelevant New-Lines"-CheckBox in QA. It would have
been nicer without this setting and that QA automatically gave you the
correct line length and that stream blocks were automatically appended as
well. I don't know any people who want them divided this way. Divided
between the tags works fine, but not like this in the middle after 2034
bytes.
I have found EmEditor to be very useful in these cases with its RegExp Find
& Replace: \r\n -> <nothing>.
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
> Too much is not a problem. The reason why I said 4000 is that each stream
> block (ie a row chunk) that is being returned is around 2034 bytes, so
> 4000 is enough).
> But regardless of the setting, if you get the XML in more than one chunk
> in the query analyzer, you will have to do some postprocessing to get rid
> of the newlines.
> Best regards
> Mcihael
> "Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
> news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>
|||Well, yes. The SQL Server 2005 integration is now much better, we even have
a hyperlink triggered XML editor build in now.
So go out and upgrade :-).
Best regards
Michael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23927VrQAGHA.832@.tk2msftngp13.phx.gbl...
> Yes a bit annoying that you have to do that yourself instead of checking
> that "Don't give me irrelevant New-Lines"-CheckBox in QA. It would have
> been nicer without this setting and that QA automatically gave you the
> correct line length and that stream blocks were automatically appended as
> well. I don't know any people who want them divided this way. Divided
> between the tags works fine, but not like this in the middle after 2034
> bytes.
> I have found EmEditor to be very useful in these cases with its RegExp
> Find & Replace: \r\n -> <nothing>.
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
>

FOR XML AUTO, ELEMENTS

I am using SQL Server 2000.
When I run SQL in Query Analyzer similar to the following, each row in the
results is truncated to 256 characters:
SELECT * FROM tblName
FOR XML AUTO, ELEMENTS
How can I prevent the output from truncating each row?
Also, is it possible for the output to be formatted with a CRLF after each
element, and appropriate indentation of elements?
Thanks
BillFirst, you can increase the limit of the result to 4000 characters. That way
you will see everything. However, since the query analyzer does not really
understand the XML, you should not attempt to use the XML there except for
doing visual checks. If you want to get the XML in a stream, use either the
ADO/ADO.Net mechanisms to get the XML stream back or use the SQLXML ISAPI.
In the later case, you would access the data through IE, and thus you would
get your pretty-printing of the XML.
Best regards
Michael
"bill" <belgie@.datamti.com> wrote in message
news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>I am using SQL Server 2000.
> When I run SQL in Query Analyzer similar to the following, each row in the
> results is truncated to 256 characters:
> SELECT * FROM tblName
> FOR XML AUTO, ELEMENTS
> How can I prevent the output from truncating each row?
> Also, is it possible for the output to be formatted with a CRLF after each
> element, and appropriate indentation of elements?
> Thanks
> Bill
>|||I always change my settings to display 8192 instead of the annoying 256
standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
> First, you can increase the limit of the result to 4000 characters. That
> way you will see everything. However, since the query analyzer does not
> really understand the XML, you should not attempt to use the XML there
> except for doing visual checks. If you want to get the XML in a stream,
> use either the ADO/ADO.Net mechanisms to get the XML stream back or use
> the SQLXML ISAPI. In the later case, you would access the data through IE,
> and thus you would get your pretty-printing of the XML.
> Best regards
> Michael
> "bill" <belgie@.datamti.com> wrote in message
> news:eHYVIBm3FHA.3952@.TK2MSFTNGP10.phx.gbl...
>|||Too much is not a problem. The reason why I said 4000 is that each stream
block (ie a row chunk) that is being returned is around 2034 bytes, so 4000
is enough).
But regardless of the setting, if you get the XML in more than one chunk in
the query analyzer, you will have to do some postprocessing to get rid of
the newlines.
Best regards
Mcihael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>I always change my settings to display 8192 instead of the annoying 256
>standard. 8192 seems to be the maximum, but perhaps I have 4192 too much?
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:%23lVFYRm3FHA.3400@.tk2msftngp13.phx.gbl...
>|||Yes a bit annoying that you have to do that yourself instead of checking
that "Don't give me irrelevant New-Lines"-CheckBox in QA. :) It would have
been nicer without this setting and that QA automatically gave you the
correct line length and that stream blocks were automatically appended as
well. I don't know any people who want them divided this way. Divided
between the tags works fine, but not like this in the middle after 2034
bytes.
I have found EmEditor to be very useful in these cases with its RegExp Find
& Replace: \r\n -> <nothing>.
Best regards
Niklas Engfelt
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
> Too much is not a problem. The reason why I said 4000 is that each stream
> block (ie a row chunk) that is being returned is around 2034 bytes, so
> 4000 is enough).
> But regardless of the setting, if you get the XML in more than one chunk
> in the query analyzer, you will have to do some postprocessing to get rid
> of the newlines.
> Best regards
> Mcihael
> "Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
> news:%23MC0RKy4FHA.2364@.TK2MSFTNGP12.phx.gbl...
>|||Well, yes. The SQL Server 2005 integration is now much better, we even have
a hyperlink triggered XML editor build in now.
So go out and upgrade :-).
Best regards
Michael
"Niklas E" <raven_tln0sp4m@.hotmail.com> wrote in message
news:%23927VrQAGHA.832@.tk2msftngp13.phx.gbl...
> Yes a bit annoying that you have to do that yourself instead of checking
> that "Don't give me irrelevant New-Lines"-CheckBox in QA. :) It would have
> been nicer without this setting and that QA automatically gave you the
> correct line length and that stream blocks were automatically appended as
> well. I don't know any people who want them divided this way. Divided
> between the tags works fine, but not like this in the middle after 2034
> bytes.
> I have found EmEditor to be very useful in these cases with its RegExp
> Find & Replace: \r\n -> <nothing>.
> Best regards
> Niklas Engfelt
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:uRu47C16FHA.3232@.TK2MSFTNGP15.phx.gbl...
>

For XML -> ADO Recordset

I am trying to create an ADO Recordset (VB 6.0 SP6) populated with the
results of a SELECT...FOR XML statement. The SQL I use is:
http://tcs_amd/xfpic?sql=SELECT * FROM Policy WHERE PolicyID='FPHM016182'
FOR XML AUTO,XMLDATA&root=Policies
This returns the Schema and Data, which I capture in strXML, and try to load
as follows:
Dim rs As New ADODB.Recordset
Dim s As New ADODB.Stream
...
s.Open
s.WriteText strXML
s.Position = 0
rs.Open s
I get the following Error on the last line:
"Recordset cannot be created from the Specified source. The source file or
stream must contain recordset data in XML if ADTG format."
I have also tried to save the XML to a file, and then do the following:
rs.Open "f:\junk\Data.xml", "Provider=MSPersist"
This gives me the following error:
Recordset cannot be created. Source XML is incomplete or invalid.
The XML from the file loads into IE without error.
Is what I am trying to do possible without much trouble, or will I need to
use MSXML? Any FAQs or articles you could point me to would be appreciated.
TIA
Mike
Mike see my response to "A Mindboggingly simple question" Below
Basically this should do what you want
Sub SaveXml()
Dim oCmd As Command
Dim oPrm As Parameter
Dim oDom As IXMLDOMDocument2
Set oDom = New DOMDocument40
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data
Source=."
oCmd.CommandText = "SQL_First"
oCmd.CommandType = adCmdStoredProc
oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024
oDom.Save "c:\temp\results.xml"
End Sub
Obviously if you dont want to persist it you can simply stream the oDom.xml
Hope this helps
Graham
"Mike Salter" <trailcreek@.hotmail.NOSPAM.com> wrote in message
news:ejlbKFycEHA.4048@.TK2MSFTNGP12.phx.gbl...
> I am trying to create an ADO Recordset (VB 6.0 SP6) populated with the
> results of a SELECT...FOR XML statement. The SQL I use is:
> http://tcs_amd/xfpic?sql=SELECT * FROM Policy WHERE PolicyID='FPHM016182'
> FOR XML AUTO,XMLDATA&root=Policies
> This returns the Schema and Data, which I capture in strXML, and try to
load
> as follows:
> Dim rs As New ADODB.Recordset
> Dim s As New ADODB.Stream
> ...
> s.Open
> s.WriteText strXML
> s.Position = 0
> rs.Open s
> I get the following Error on the last line:
> "Recordset cannot be created from the Specified source. The source file
or
> stream must contain recordset data in XML if ADTG format."
> I have also tried to save the XML to a file, and then do the following:
> rs.Open "f:\junk\Data.xml", "Provider=MSPersist"
> This gives me the following error:
> Recordset cannot be created. Source XML is incomplete or invalid.
> The XML from the file loads into IE without error.
> Is what I am trying to do possible without much trouble, or will I need to
> use MSXML? Any FAQs or articles you could point me to would be
appreciated.
> TIA
> --
> Mike
>
|||Graham:
I tried it, and am getting an error still. The Code is as follows:
Dim oCmd As Command
Dim oDom As IXMLDOMDocument2
Dim rs As New ADODB.Recordset
Set oDom = New DOMDocument40
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated " & _
"Security=SSPI;Persist Security Info=False;Initial
Catalog=Northwind;Data " & _
"Source=tcs2003s"
oCmd.CommandText = "Employees_sp"
oCmd.CommandType = adCmdStoredProc
oCmd.Properties("Output Stream") = oDom
' Added next line to add a root node
oCmd.Properties("xml root") = "root"
oCmd.Execute , , 1024
oDom.save "f:\junk\results.xml"
' I get error "Recordset cannot be created. Source XML is incomplete or
invalid." on next line (err # -2147467259)
' although the xml loads into IE
rs.Open "f:\junk\results.xml", "Provider=MSPersist"
Employees_sp source:
CREATE PROCEDURE Employees_sp
AS
SELECT * FROM Employees FOR XML AUTO, XMLDATA
I am using ADO 2.8
Any thoughts?
Thanks
Mike
"Graham Shaw" <Graham@.somewhere.com> wrote in message
news:8%aNc.643$C85.83@.newsfe1-gui.ntli.net...
> Mike see my response to "A Mindboggingly simple question" Below
> Basically this should do what you want
> Sub SaveXml()
> Dim oCmd As Command
> Dim oPrm As Parameter
> Dim oDom As IXMLDOMDocument2
> Set oDom = New DOMDocument40
> Set oCmd = New Command
> oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data
> Source=."
> oCmd.CommandText = "SQL_First"
> oCmd.CommandType = adCmdStoredProc
> oCmd.Properties("Output Stream") = oDom
> oCmd.Execute , , 1024
> oDom.Save "c:\temp\results.xml"
> End Sub
> Obviously if you dont want to persist it you can simply stream the
oDom.xml[vbcol=seagreen]
> Hope this helps
> Graham
> "Mike Salter" <trailcreek@.hotmail.NOSPAM.com> wrote in message
> news:ejlbKFycEHA.4048@.TK2MSFTNGP12.phx.gbl...
PolicyID='FPHM016182'[vbcol=seagreen]
> load
> or
to
> appreciated.
>
|||Mike,
The xml you are getting is not a persisted recordset it is simply pure xml
therefore you can't load it into a recordset. If all you want is a recordset
then just use a plain sp e.g.
CREATE PROCEDURE Employees_sp
AS
SELECT * FROM Employees
Dim oCmd As Command
Dim rs As New ADODB.Recordset
Set oCmd = New Command
oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated " & _
"Security=SSPI;Persist Security Info=False;Initial
Catalog=Northwind;Data " & _
"Source=tcs2003s"
oCmd.CommandText = "Employees_sp"
oCmd.CommandType = adCmdStoredProc
set rs=oCmd.Execute( )
then you can save the resulting recordset as xml with
rs.save "f:\junk\result.xml", 1
rs.close
and later do
rs.Open "f:\junk\results.xml", "Provider=MSPersist"
"Mike Salter" <trailcreek@.hotmail.NOSPAM.com> wrote in message
news:uR922v%23cEHA.3632@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Graham:
> I tried it, and am getting an error still. The Code is as follows:
> Dim oCmd As Command
> Dim oDom As IXMLDOMDocument2
> Dim rs As New ADODB.Recordset
> Set oDom = New DOMDocument40
> Set oCmd = New Command
> oCmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated " & _
> "Security=SSPI;Persist Security Info=False;Initial
> Catalog=Northwind;Data " & _
> "Source=tcs2003s"
> oCmd.CommandText = "Employees_sp"
> oCmd.CommandType = adCmdStoredProc
> oCmd.Properties("Output Stream") = oDom
> ' Added next line to add a root node
> oCmd.Properties("xml root") = "root"
> oCmd.Execute , , 1024
> oDom.save "f:\junk\results.xml"
> ' I get error "Recordset cannot be created. Source XML is incomplete or
> invalid." on next line (err # -2147467259)
> ' although the xml loads into IE
> rs.Open "f:\junk\results.xml", "Provider=MSPersist"
> Employees_sp source:
> CREATE PROCEDURE Employees_sp
> AS
> SELECT * FROM Employees FOR XML AUTO, XMLDATA
> I am using ADO 2.8
> Any thoughts?
> Thanks
> --
> Mike
> "Graham Shaw" <Graham@.somewhere.com> wrote in message
> news:8%aNc.643$C85.83@.newsfe1-gui.ntli.net...
> oDom.xml
> PolicyID='FPHM016182'
to[vbcol=seagreen]
file[vbcol=seagreen]
following:[vbcol=seagreen]
need
> to
>