| Author |
General XML SQL 2005 Question
|
|
| Sanjay Narayan 2006-10-24, 6:49 pm |
| 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
| |
| Peter W. DeBetta 2006-10-24, 6:49 pm |
| 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@discus
sions.microsoft.com> wrote in message
news:1B0F12C7-5100-4B8E-B547- 09982E733E22@microso
ft.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
>
>
| |
| Roger Wolter[MSFT] 2006-10-24, 6:49 pm |
| 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@discus
sions.microsoft.com> wrote in message
news:1B0F12C7-5100-4B8E-B547- 09982E733E22@microso
ft.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
>
>
| |
| Sanjay Narayan 2006-10-25, 6:06 am |
| 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@discus
sions.microsoft.com> wrote in message
> news:1B0F12C7-5100-4B8E-B547- 09982E733E22@microso
ft.com...
>
>
>
|
|
|
|