Geocoding in SQL Server 2000 DTS Package

Anyone have any examples or information on how to best accomplish
geocoding in a SQL Server 2000 DTS package?
Spencer"stabbert" <spencer@.tabbert.net> wrote in message
news:1152191801.603777.125550@.j8g2000cwa.googlegroups.com...
> Anyone have any examples or information on how to best accomplish
> geocoding in a SQL Server 2000 DTS package?
> Spencer
>
How are you doing your geocoding? Are you trying to store lats and longs?
A key that maps to lats and longs? Are you using a zipcode, or county, or
state value to determine your geocoding?
Rick Sawtell
MCT, MCSD, MCDBA|||We are attempting to store lattitude and longitude based upon full US
addresses. We need the lattitude and longitude as the input will be
the address.
Spencer
Rick Sawtell wrote:
> "stabbert" <spencer@.tabbert.net> wrote in message
> news:1152191801.603777.125550@.j8g2000cwa.googlegroups.com...
> How are you doing your geocoding? Are you trying to store lats and longs?
> A key that maps to lats and longs? Are you using a zipcode, or county, or
> state value to determine your geocoding?
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA|||Here's as start, but you need SQL Server 2005.
http://msdn.microsoft.com/library/d...
alFuncSQL.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"stabbert" <spencer@.tabbert.net> wrote in message
news:1152231254.150274.286890@.s13g2000cwa.googlegroups.com...
We are attempting to store lattitude and longitude based upon full US
addresses. We need the lattitude and longitude as the input will be
the address.
Spencer
Rick Sawtell wrote:
> "stabbert" <spencer@.tabbert.net> wrote in message
> news:1152191801.603777.125550@.j8g2000cwa.googlegroups.com...
> How are you doing your geocoding? Are you trying to store lats and longs?
> A key that maps to lats and longs? Are you using a zipcode, or county, or
> state value to determine your geocoding?
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA|||I have written a VBscript that will be embeded in a DTS package that
will use the MSXML2.ServerXMLHTTP.3.0 object to make calls to the
Google and Yahoo API used for geocoding. The simple HTTP calls are all
that I need and are simple. I can then get the elements I need out of
the XML returned. I plan to expand this eventually ontok.com and
geocoder.us for commercial type applications and they offer the same
simple HTTP REST interface.
Spence

Geocoding in SQL Server 2000 DTS Package

Anyone have any examples or information on how to best accomplish
geocoding in a SQL Server 2000 DTS package?
Spencer"stabbert" <spencer@.tabbert.net> wrote in message
news:1152191801.603777.125550@.j8g2000cwa.googlegroups.com...
> Anyone have any examples or information on how to best accomplish
> geocoding in a SQL Server 2000 DTS package?
> Spencer
>
How are you doing your geocoding? Are you trying to store lats and longs?
A key that maps to lats and longs? Are you using a zipcode, or county, or
state value to determine your geocoding?
Rick Sawtell
MCT, MCSD, MCDBA|||We are attempting to store lattitude and longitude based upon full US
addresses. We need the lattitude and longitude as the input will be
the address.
Spencer
Rick Sawtell wrote:
> "stabbert" <spencer@.tabbert.net> wrote in message
> news:1152191801.603777.125550@.j8g2000cwa.googlegroups.com...
> > Anyone have any examples or information on how to best accomplish
> > geocoding in a SQL Server 2000 DTS package?
> > Spencer
> >
> How are you doing your geocoding? Are you trying to store lats and longs?
> A key that maps to lats and longs? Are you using a zipcode, or county, or
> state value to determine your geocoding?
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA|||Here's as start, but you need SQL Server 2005.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/TblValFuncSQL.asp
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"stabbert" <spencer@.tabbert.net> wrote in message
news:1152231254.150274.286890@.s13g2000cwa.googlegroups.com...
We are attempting to store lattitude and longitude based upon full US
addresses. We need the lattitude and longitude as the input will be
the address.
Spencer
Rick Sawtell wrote:
> "stabbert" <spencer@.tabbert.net> wrote in message
> news:1152191801.603777.125550@.j8g2000cwa.googlegroups.com...
> > Anyone have any examples or information on how to best accomplish
> > geocoding in a SQL Server 2000 DTS package?
> > Spencer
> >
> How are you doing your geocoding? Are you trying to store lats and longs?
> A key that maps to lats and longs? Are you using a zipcode, or county, or
> state value to determine your geocoding?
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA|||I have written a VBscript that will be embeded in a DTS package that
will use the MSXML2.ServerXMLHTTP.3.0 object to make calls to the
Google and Yahoo API used for geocoding. The simple HTTP calls are all
that I need and are simple. I can then get the elements I need out of
the XML returned. I plan to expand this eventually ontok.com and
geocoder.us for commercial type applications and they offer the same
simple HTTP REST interface.
Spence

generating an unique number within SSIS - looking for good examples

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!

Sergio

Here is some script to generate a new GUID.

Code Snippet

Public Sub Main()

' Generate a globally unique identifier

Dim myGuid As Guid = Guid.NewGuid()

MsgBox(myGuid.ToString())

Dts.TaskResult = Dts.Results.Success

End Sub

|||

Sergio wrote:

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!

Sergio

Like this? http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

What do you mean, "generate a new identity value"?

General XML SQL 2005 Question

Hi All
I am new to Sql Xml and currently working through some examples to
understand XML processing in SQL2005 because we are planning to use SQL 2005
with XML. If this is not the right place to ask this question then my
apologies.
Currently using a XML Schema. Schema is
- <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="logRecord" type="logRecordType" />
- <xsd:complexType name="errorType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:sequence>
<xsd:element name="message" type="xsd:string" />
<xsd:element name="module" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="number" type="xsd:int" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:complexType name="informationType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:sequence>
<xsd:element name="message" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="flag" type="flagEnum" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:complexType name="logRecordType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:choice maxOccurs="unbounded">
<xsd:element name="information" type="informationType" />
<xsd:element name="error" type="errorType" />
<xsd:element name="post" type="postType" />
</xsd:choice>
<xsd:attribute name="machine" type="xsd:string" />
<xsd:attribute name="timestamp" type="xsd:dateTime" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:complexType name="postType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:sequence>
<xsd:element name="moreInformation" type="xsd:string" minOccurs="0" />
</xsd:sequence>
<xsd:attribute name="eventType" type="eventEnum" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:simpleType name="eventEnum">
- <xsd:restriction base="xsd:string">
<xsd:enumeration value="appStart" />
<xsd:enumeration value="appClose" />
<xsd:enumeration value="logIn" />
<xsd:enumeration value="logOut" />
</xsd:restriction>
</xsd:simpleType>
- <xsd:simpleType name="flagEnum">
- <xsd:restriction base="xsd:string">
<xsd:enumeration value="warning" />
<xsd:enumeration value="information" />
<xsd:enumeration value="failure" />
<xsd:enumeration value="custom" />
</xsd:restriction>
</xsd:simpleType>
</xsd:schema>
When I execute
select top 1 * from Universallog For XML Auto
Output I get is
<Universallog ID="1" LogDateTime="2006-09-14T15:50:56.210"
ApplicationName="SalesApp">
<LogRecord>
<logRecord machine="server1" timestamp="2000-01-12T12:13:14Z">
<post eventType="appStart">
<moreInformation>All Services starting</moreInformation>
</post>
</logRecord>
</LogRecord>
</Universallog>
Question
I do not understand where is the Element <LogRecord> is coming from as it
is not defined in the XML Schema
Any feed back provided is greatly appreciated.
Thank you
Regards
Sanjay
You need to send more info such as the definition of the UniversalLog table.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Sanjay Narayan" <SanjayNarayan@.discussions.microsoft.com> wrote in message
news:1B0F12C7-5100-4B8E-B547-09982E733E22@.microsoft.com...
> Hi All
> I am new to Sql Xml and currently working through some examples to
> understand XML processing in SQL2005 because we are planning to use SQL
> 2005
> with XML. If this is not the right place to ask this question then my
> apologies.
>
> Currently using a XML Schema. Schema is
> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
> <xsd:element name="logRecord" type="logRecordType" />
> - <xsd:complexType name="errorType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> <xsd:element name="module" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="number" type="xsd:int" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="informationType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="flag" type="flagEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="logRecordType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:choice maxOccurs="unbounded">
> <xsd:element name="information" type="informationType" />
> <xsd:element name="error" type="errorType" />
> <xsd:element name="post" type="postType" />
> </xsd:choice>
> <xsd:attribute name="machine" type="xsd:string" />
> <xsd:attribute name="timestamp" type="xsd:dateTime" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="postType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="moreInformation" type="xsd:string" minOccurs="0" />
> </xsd:sequence>
> <xsd:attribute name="eventType" type="eventEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:simpleType name="eventEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="appStart" />
> <xsd:enumeration value="appClose" />
> <xsd:enumeration value="logIn" />
> <xsd:enumeration value="logOut" />
> </xsd:restriction>
> </xsd:simpleType>
> - <xsd:simpleType name="flagEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="warning" />
> <xsd:enumeration value="information" />
> <xsd:enumeration value="failure" />
> <xsd:enumeration value="custom" />
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:schema>
>
> When I execute
> select top 1 * from Universallog For XML Auto
> Output I get is
> <Universallog ID="1" LogDateTime="2006-09-14T15:50:56.210"
> ApplicationName="SalesApp">
> <LogRecord>
> <logRecord machine="server1" timestamp="2000-01-12T12:13:14Z">
> <post eventType="appStart">
> <moreInformation>All Services starting</moreInformation>
> </post>
> </logRecord>
> </LogRecord>
> </Universallog>
>
> Question
> I do not understand where is the Element <LogRecord> is coming from as it
> is not defined in the XML Schema
> Any feed back provided is greatly appreciated.
> Thank you
> Regards
> Sanjay
>
|||Your statement: 'select top 1 * from Universallog For XML Auto' does not
specify the schema anywhere. How were you expecting the schema to interact
with SQL Server? SQL Server 2005 allows you to define an XML datatype that
conforms to a particular schema but the closest SQL XML comes to this is
xpath queries using a mapping schema. For XML Explicit will allow you to
define what you want the output to look like but AUTO produces an XML
document based on the columns involved in the select statement.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sanjay Narayan" <SanjayNarayan@.discussions.microsoft.com> wrote in message
news:1B0F12C7-5100-4B8E-B547-09982E733E22@.microsoft.com...
> Hi All
> I am new to Sql Xml and currently working through some examples to
> understand XML processing in SQL2005 because we are planning to use SQL
> 2005
> with XML. If this is not the right place to ask this question then my
> apologies.
>
> Currently using a XML Schema. Schema is
> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
> <xsd:element name="logRecord" type="logRecordType" />
> - <xsd:complexType name="errorType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> <xsd:element name="module" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="number" type="xsd:int" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="informationType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="flag" type="flagEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="logRecordType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:choice maxOccurs="unbounded">
> <xsd:element name="information" type="informationType" />
> <xsd:element name="error" type="errorType" />
> <xsd:element name="post" type="postType" />
> </xsd:choice>
> <xsd:attribute name="machine" type="xsd:string" />
> <xsd:attribute name="timestamp" type="xsd:dateTime" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="postType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="moreInformation" type="xsd:string" minOccurs="0" />
> </xsd:sequence>
> <xsd:attribute name="eventType" type="eventEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:simpleType name="eventEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="appStart" />
> <xsd:enumeration value="appClose" />
> <xsd:enumeration value="logIn" />
> <xsd:enumeration value="logOut" />
> </xsd:restriction>
> </xsd:simpleType>
> - <xsd:simpleType name="flagEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="warning" />
> <xsd:enumeration value="information" />
> <xsd:enumeration value="failure" />
> <xsd:enumeration value="custom" />
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:schema>
>
> When I execute
> select top 1 * from Universallog For XML Auto
> Output I get is
> <Universallog ID="1" LogDateTime="2006-09-14T15:50:56.210"
> ApplicationName="SalesApp">
> <LogRecord>
> <logRecord machine="server1" timestamp="2000-01-12T12:13:14Z">
> <post eventType="appStart">
> <moreInformation>All Services starting</moreInformation>
> </post>
> </logRecord>
> </LogRecord>
> </Universallog>
>
> Question
> I do not understand where is the Element <LogRecord> is coming from as it
> is not defined in the XML Schema
> Any feed back provided is greatly appreciated.
> Thank you
> Regards
> Sanjay
>
|||Thanks. It make sense now and I can now understand why the output is as such.
"Roger Wolter[MSFT]" wrote:

> Your statement: 'select top 1 * from Universallog For XML Auto' does not
> specify the schema anywhere. How were you expecting the schema to interact
> with SQL Server? SQL Server 2005 allows you to define an XML datatype that
> conforms to a particular schema but the closest SQL XML comes to this is
> xpath queries using a mapping schema. For XML Explicit will allow you to
> define what you want the output to look like but AUTO produces an XML
> document based on the columns involved in the select statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Sanjay Narayan" <SanjayNarayan@.discussions.microsoft.com> wrote in message
> news:1B0F12C7-5100-4B8E-B547-09982E733E22@.microsoft.com...
>
>

General XML SQL 2005 Question

Hi All
I am new to Sql Xml and currently working through some examples to
understand XML processing in SQL2005 because we are planning to use SQL 2005
with XML. If this is not the right place to ask this question then my
apologies.
Currently using a XML Schema. Schema is
- <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="logRecord" type="logRecordType" />
- <xsd:complexType name="errorType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:sequence>
<xsd:element name="message" type="xsd:string" />
<xsd:element name="module" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="number" type="xsd:int" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:complexType name="informationType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:sequence>
<xsd:element name="message" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="flag" type="flagEnum" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:complexType name="logRecordType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:choice maxOccurs="unbounded">
<xsd:element name="information" type="informationType" />
<xsd:element name="error" type="errorType" />
<xsd:element name="post" type="postType" />
</xsd:choice>
<xsd:attribute name="machine" type="xsd:string" />
<xsd:attribute name="timestamp" type="xsd:dateTime" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:complexType name="postType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:sequence>
<xsd:element name="moreInformation" type="xsd:string" minOccurs="0" />
</xsd:sequence>
<xsd:attribute name="eventType" type="eventEnum" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:simpleType name="eventEnum">
- <xsd:restriction base="xsd:string">
<xsd:enumeration value="appStart" />
<xsd:enumeration value="appClose" />
<xsd:enumeration value="logIn" />
<xsd:enumeration value="logOut" />
</xsd:restriction>
</xsd:simpleType>
- <xsd:simpleType name="flagEnum">
- <xsd:restriction base="xsd:string">
<xsd:enumeration value="warning" />
<xsd:enumeration value="information" />
<xsd:enumeration value="failure" />
<xsd:enumeration value="custom" />
</xsd:restriction>
</xsd:simpleType>
</xsd:schema>
When I execute
select top 1 * from Universallog For XML Auto
Output I get is
<Universallog ID="1" LogDateTime="2006-09-14T15:50:56.210"
ApplicationName="SalesApp">
<LogRecord>
<logRecord machine="server1" timestamp="2000-01-12T12:13:14Z">
<post eventType="appStart">
<moreInformation>All Services starting</moreInformation>
</post>
</logRecord>
</LogRecord>
</Universallog>
Question
I do not understand where is the Element <LogRecord> is coming from as it
is not defined in the XML Schema
Any feed back provided is greatly appreciated.
Thank you
Regards
SanjayYou need to send more info such as the definition of the UniversalLog table.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Sanjay Narayan" <SanjayNarayan@.discussions.microsoft.com> wrote in message
news:1B0F12C7-5100-4B8E-B547-09982E733E22@.microsoft.com...
> Hi All
> I am new to Sql Xml and currently working through some examples to
> understand XML processing in SQL2005 because we are planning to use SQL
> 2005
> with XML. If this is not the right place to ask this question then my
> apologies.
>
> Currently using a XML Schema. Schema is
> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
> <xsd:element name="logRecord" type="logRecordType" />
> - <xsd:complexType name="errorType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> <xsd:element name="module" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="number" type="xsd:int" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="informationType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="flag" type="flagEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="logRecordType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:choice maxOccurs="unbounded">
> <xsd:element name="information" type="informationType" />
> <xsd:element name="error" type="errorType" />
> <xsd:element name="post" type="postType" />
> </xsd:choice>
> <xsd:attribute name="machine" type="xsd:string" />
> <xsd:attribute name="timestamp" type="xsd:dateTime" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="postType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="moreInformation" type="xsd:string" minOccurs="0" />
> </xsd:sequence>
> <xsd:attribute name="eventType" type="eventEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:simpleType name="eventEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="appStart" />
> <xsd:enumeration value="appClose" />
> <xsd:enumeration value="logIn" />
> <xsd:enumeration value="logOut" />
> </xsd:restriction>
> </xsd:simpleType>
> - <xsd:simpleType name="flagEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="warning" />
> <xsd:enumeration value="information" />
> <xsd:enumeration value="failure" />
> <xsd:enumeration value="custom" />
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:schema>
>
> When I execute
> select top 1 * from Universallog For XML Auto
> Output I get is
> <Universallog ID="1" LogDateTime="2006-09-14T15:50:56.210"
> ApplicationName="SalesApp">
> <LogRecord>
> <logRecord machine="server1" timestamp="2000-01-12T12:13:14Z">
> <post eventType="appStart">
> <moreInformation>All Services starting</moreInformation>
> </post>
> </logRecord>
> </LogRecord>
> </Universallog>
>
> Question
> I do not understand where is the Element <LogRecord> is coming from as it
> is not defined in the XML Schema
> Any feed back provided is greatly appreciated.
> Thank you
> Regards
> Sanjay
>|||Your statement: 'select top 1 * from Universallog For XML Auto' does not
specify the schema anywhere. How were you expecting the schema to interact
with SQL Server? SQL Server 2005 allows you to define an XML datatype that
conforms to a particular schema but the closest SQL XML comes to this is
xpath queries using a mapping schema. For XML Explicit will allow you to
define what you want the output to look like but AUTO produces an XML
document based on the columns involved in the select statement.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sanjay Narayan" <SanjayNarayan@.discussions.microsoft.com> wrote in message
news:1B0F12C7-5100-4B8E-B547-09982E733E22@.microsoft.com...
> Hi All
> I am new to Sql Xml and currently working through some examples to
> understand XML processing in SQL2005 because we are planning to use SQL
> 2005
> with XML. If this is not the right place to ask this question then my
> apologies.
>
> Currently using a XML Schema. Schema is
> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
> <xsd:element name="logRecord" type="logRecordType" />
> - <xsd:complexType name="errorType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> <xsd:element name="module" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="number" type="xsd:int" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="informationType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="flag" type="flagEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="logRecordType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:choice maxOccurs="unbounded">
> <xsd:element name="information" type="informationType" />
> <xsd:element name="error" type="errorType" />
> <xsd:element name="post" type="postType" />
> </xsd:choice>
> <xsd:attribute name="machine" type="xsd:string" />
> <xsd:attribute name="timestamp" type="xsd:dateTime" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="postType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="moreInformation" type="xsd:string" minOccurs="0" />
> </xsd:sequence>
> <xsd:attribute name="eventType" type="eventEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:simpleType name="eventEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="appStart" />
> <xsd:enumeration value="appClose" />
> <xsd:enumeration value="logIn" />
> <xsd:enumeration value="logOut" />
> </xsd:restriction>
> </xsd:simpleType>
> - <xsd:simpleType name="flagEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="warning" />
> <xsd:enumeration value="information" />
> <xsd:enumeration value="failure" />
> <xsd:enumeration value="custom" />
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:schema>
>
> When I execute
> select top 1 * from Universallog For XML Auto
> Output I get is
> <Universallog ID="1" LogDateTime="2006-09-14T15:50:56.210"
> ApplicationName="SalesApp">
> <LogRecord>
> <logRecord machine="server1" timestamp="2000-01-12T12:13:14Z">
> <post eventType="appStart">
> <moreInformation>All Services starting</moreInformation>
> </post>
> </logRecord>
> </LogRecord>
> </Universallog>
>
> Question
> I do not understand where is the Element <LogRecord> is coming from as it
> is not defined in the XML Schema
> Any feed back provided is greatly appreciated.
> Thank you
> Regards
> Sanjay
>|||Thanks. It make sense now and I can now understand why the output is as such
.
"Roger Wolter[MSFT]" wrote:

> Your statement: 'select top 1 * from Universallog For XML Auto' does not
> specify the schema anywhere. How were you expecting the schema to interac
t
> with SQL Server? SQL Server 2005 allows you to define an XML datatype tha
t
> conforms to a particular schema but the closest SQL XML comes to this is
> xpath queries using a mapping schema. For XML Explicit will allow you to
> define what you want the output to look like but AUTO produces an XML
> document based on the columns involved in the select statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Sanjay Narayan" <SanjayNarayan@.discussions.microsoft.com> wrote in messag
e
> news:1B0F12C7-5100-4B8E-B547-09982E733E22@.microsoft.com...
>
>