Re: Annotated schema with views?

Yes, we do support attribute groups in SqlXml. You can annotate them as
normal attributes and the annotations will be resolved based on the context
of attributegroup ref..

As far as I undertand your technique, you are executing multiple queries to
construct one Xml document.instead of executing one complex query. That
might be useful since the complexity of our FOR XML explicit queries
increases with the square of complex type elements.

Michael may talk about if SQL 2005 will offer solutions that will perform
better in deep hierarchies.

--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.



"Joe" <morbidcamel@xxxxxxx> wrote in message
news:eKARm$6eFHA.3280@xxxxxxxxxxxxxxxxxxxxxxx
>I have attached some XML sample. I cannot give you the schema because there
>is some traid secrets in there :) It is based on the OMG MOF model and is
>represented with interfaces and classes. Note that XSLT is used to
>transform XML to XMI etc.
>
> When requestig the fragments, they always have a PID (Parent Id) an so
> doing I can add it to the parent element's contents. As you can see, the
> XML can nest quite deeply. All XML are serialized into classes. The XML
> can also be serialized to a DataSet for generating Diffgrams and/or
> SqlAdaptors... The problem is, I have to add more model elements to my
> schema, and when requesting a <Pkg> (package) element there can be up to
> 12 different complex types retrieved.
>
> In my case a typical X-Path query looks like
> /Nms[@ID='f5fde8f9-b359-5eca-71b6-8012ce027c32'] and for multiple elements
> /Nms[@ID='000000ea-00e0-00fa-b0ca-d0bad000eae0' or
> @ID='00000000-00fa-00b0-eae0-cad0bad00001' or
> @ID='e4ecf9eb-a252-4f0b-8a47-5bf305e36d27' or
> @ID='f5fde8f9-b359-5eca-71b6-8012ce027c33']. When the fragment is
> selected, an additional query is done to get a collection of light-weight
> "descriptors" of parent elements, which in turn is used to construct the
> parent elements if they weren't already constructed. SQLXML and .NET
> Serializer reduces the ETL time significantly. All I want though is super
> fast query times, which up until now was quite impressive. The second
> version of my framework is only due in another 6 months so there is still
> some time to find a solution and all my hopes is currently on SQL2005.
>
> As far as the attribute groups are involved I am refering to something
> like :
>
> <xs:attributeGroup name="XMI.element.att">
> <xs:annotation>
> <xs:documentation>
> XMI.element.att defines the attributes that each XML element
> that corresponds to a metamodel class must have to conform to
> the XMI specification.
> </xs:documentation>
> </xs:annotation>
> <xs:attribute name="xmi.id" type="xs:ID"/>
> <xs:attribute name="xmi.label" type="xs:string"/>
> <xs:attribute name="xmi.uuid" type="xs:string"/>
> </xs:attributeGroup>
>
> Can SQLXML annotations added to this attribute group be interpreted by the
> SQLXML engine.
>
>
> Here is sample XML requested from SQL.
> ....
> <Nms N="Synap-c" ID="00000000-00fa-00b0-eae0-cad0bad00001"
> PID="000000ea-00e0-00fa-b0ca-d0bad000eae0">
> <An>SiloFx Synap-c suite of products</An>
> <Tag>[Tank].[Synap-c]</Tag>
> <NmsC>
> <Nms N="Administrator" ID="e4ecf9eb-a252-4f0b-8a47-5bf305e36d27"
> PID="00000000-00fa-00b0-eae0-cad0bad00001">
> <An>{1}:{2}</An>
> <Tag>[Tank].[Synap-c].[Administrator]</Tag>
> <NmsC>
> <Nms N="EAE" ID="f5fde8f9-b359-5eca-71b6-8012ce027c33"
> PID="e4ecf9eb-a252-4f0b-8a47-5bf305e36d27">
> <An>SiloFx Synap-c Enterprise Architect Edition (EAE) at
> Administrator</An>
> <Tag>[Tank].[Synap-c].[Administrator].[EAE]</Tag>
> <NmsC>
> <Pkg N="Types" ID="f5fde8f9-b359-5eca-71b6-8012ce027f30"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c33" V="Internal" A="false" R="true"
> L="false" B="false">
> <An>Administrator: Synap-c EAE types used in projects
> and ontologies</An>
> <PkgC />
> </Pkg>
> <Nms N="Projects"
> ID="f5fde8f9-b359-5eca-71b6-8012ce027c31"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c33">
> <An>Administrator: Synap-c EAE types used in projects
> and ontologies</An>
>
> <Tag>[Tank].[Synap-c].[Administrator].[EAE].[Projects]</Tag>
> <NmsC>
> <Pkg N="Application Architecture"
> ID="54968e3e-8330-46e2-b2df-4c15e6e30c2f"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="Application Architecture"
> ID="fce438aa-25e4-4f88-bd26-0a92ac82627c"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="As Is"
> ID="9cfe0641-8e53-4586-bd72-d397fd890b28"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="SCM Organisational Structure"
> ID="57deba4b-a86e-4a7c-96b7-4d9a0e0deb09"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="true">
> <PkgC />
> </Pkg>
> <Pkg N="Application Architecture"
> ID="aaf77499-5968-46e3-ab32-887545cfaedd"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="Application Architecture"
> ID="68cd9da0-765d-4958-ba12-95c5ae9e2860"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="Technical Architecture"
> ID="a6e957b1-f5ef-457d-bf68-ec95c9281899"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC />
> </Pkg>
> <Pkg N="Created on 2005/06/27 08:48:05 AM 872"
> ID="555da19a-aa21-4ed4-9694-915f134d48f3"
> PID="f5fde8f9-b359-5eca-71b6-8012ce027c31" V="Public" A="false" R="true"
> L="false" B="false">
> <PkgC>
> <Asc N="Links to"
> ID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
> PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Private" A="false" R="true"
> L="true" AT="DependsOn">
> <Tag>PX=0PY=0SW=1SH=1D=8|2|1|1|1|</Tag>
> <AscC>
> <AsE N="(Shop - Instance 1*)"
> ID="c5529649-0672-cae5-7a71-691db0d70c90"
> PID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
> TID="b75471b1-e5f6-455a-9857-4776c6820dff" C="true" M="1|1|1|0|0" A="None"
> Nv="false" OID="b65c7879-2f74-cb4c-78a3-ca5e51774b3a" />
> <AsE N="P(Shop - Instance 1*)"
> ID="b65c7879-2f74-cb4c-78a3-ca5e51774b3a"
> PID="32c627b8-2344-4f7f-a2e6-eeabb61541af"
> TID="445a1f81-ccf0-44f3-9a05-e4b5a722ca55" C="true" M="1|1|1|0|0" A="None"
> Nv="true" OID="c5529649-0672-cae5-7a71-691db0d70c90" />
> </AscC>
> </Asc>
> <Asc N="Buys goods - Instance"
> ID="920109d7-82b3-48ea-900e-a0a0b44a7768"
> PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Private" A="false" R="true"
> L="true" AT="DependsOn">
> <Tag>PX=0PY=0SW=1SH=1D=8|2|1|1|1|</Tag>
> <AscC>
> <AsE N="(Buys goods - Instance*)"
> ID="361f87ab-4c73-c4f0-6ad3-176260208799"
> PID="920109d7-82b3-48ea-900e-a0a0b44a7768"
> TID="64de4e3c-8e80-4cda-ba9d-7782942ab0b1" C="true" M="1|1|1|0|0" A="None"
> Nv="false" OID="c8cf4d1d-95df-cdce-7ccb-0486e947e2bc" />
> <AsE N="P(Buys goods - Instance*)"
> ID="c8cf4d1d-95df-cdce-7ccb-0486e947e2bc"
> PID="920109d7-82b3-48ea-900e-a0a0b44a7768"
> TID="1a8e048a-d72c-45e4-ac85-64e61dcd5594" C="true" M="1|1|1|0|0" A="None"
> Nv="true" OID="361f87ab-4c73-c4f0-6ad3-176260208799" />
> </AscC>
> </Asc>
> <Cls N="Shop - Instance 1"
> ID="0dae96ef-3e78-48b3-ba27-60d735345725"
> PID="555da19a-aa21-4ed4-9694-915f134d48f3" V="Public" A="false" R="false"
> L="true" S="false" B="false">
> <ClsC />
> </Cls>
> ...
>
> "Bertan ARI [MSFT]" <bertan@xxxxxxxxxxxxxxxxxxxx> wrote in message
> news:Okj2O53eFHA.2556@xxxxxxxxxxxxxxxxxxxxxxx
>> Question 1:
>> There are no plans for using FOR XML PATH for SQL 2005. SQLXML will use
>> FOR XML EXPLICIT as it did in earlier releases..
>>
>> Question 2:
>> In Sql 2005, there are no changes in the way SQLXML generate queries.
>>
>> For the complexity brought by the elements, if you use simple type
>> elements in an xsd:sequence block, we will treat them as complex type
>> elements and generate a select statement for it. This is necessary to
>> preserve the order of the elements. If you specify the simple type
>> elements in xsd:all group, it will be treated like an attribute. On the
>> other hand, there are some other cases, a simple type element might be
>> interpreted as complex type elements so I would recommend using
>> attributes vs elements.
>>
>> If your schema is deep and consists of several complex type elements, the
>> generate FOR XML query will be also big. I couldn't understand how you
>> can partially generate hierarchies and combine them. Don't you need to
>> find the relevant parent Xml element to insert the Xml fragments? We
>> would love to hear more about your solution.
>>
>> Question 3:
>>
>> Updategrams have support for identity-columns. You may either use
>> updg:at-identity :
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/updategram_375f.asp
>>
>> or use the sql:identity annoations in schema to specify the identity
>> columns.
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/ssxsdannotations_7j03.asp
>>
>> Let us know if these solutions don't work for you.
>>
>> Question 4:
>> Question 5:
>> Ditto Michael here.
>>
>> --
>> Bertan ARI
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>
>>
>> "Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:%23KzUBbTeFHA.2984@xxxxxxxxxxxxxxxxxxxxxxx
>>> See below for the answers that I know (I am not the owner of the SQLXML
>>> component for a couple of years now).
>>>
>>> Best regards
>>> Michael
>>>
>>> "Joe" <morbidcamel@xxxxxxx> wrote in message
>>> news:uc9DJzMeFHA.3620@xxxxxxxxxxxxxxxxxxxxxxx
>>>> OK, thank you for the advice. I got this information of existence
>>>> checks from the following article
>>>> http://support.microsoft.com/default.aspx?scid=kb;en-us;813955
>>>>
>>>> I have another couple of questions though.
>>>>
>>>> Question 1:
>>>> Will SQL 2005 support SQLXML natively and will the generated queries
>>>> use FOR XML PATH instead of the bulky FOR XML EXPLICIT. I know of the
>>>> XML columns, but I don't want to redo my tables necessarily.
>>>>
>>>
>>> SQL 2005 will ship with SQLXML 4.0 which is SQLXML 3.0 SP2 with support
>>> for the new datatypes and minus the IIS ISAPI for exposing templates and
>>> queries through IIS (use ASP.Net instead is the recommendation).
>>>
>>> I don't know whether they will start using FOR XML PATH, but given the
>>> cost of rewriting, I would assume not.
>>>
>>>> Question 2:
>>>> I'm running into limitations in terms of performace with my XSD Schema
>>>> because there is simply a lot of elements.
>>>>
>>>> I changed my XML format to be attribute centric and also added a lot of
>>>> clustered indexes on the keys and selected elements. This speeded up
>>>> tremendously. The problem is I have to add more elements in future and
>>>> the containment hierarchy is becoming huge. I ended up writting some
>>>> logic to query elements in fragments and add children manually. Will
>>>> this be better in SQL 2005 (if question 1 is "yes" ofcourse)?
>>>
>>> I am not sure that I have enough information about your scenario to
>>> provide you good feedback. But I would assume that the SQLXML team is
>>> interested in understanding your scenario and pain points.
>>>
>>>> Question 3:
>>>> UpdateGrams doesn't work well with identity columns. I ended using
>>>> GUIDs instead. This is fine in my current project, but I have another
>>>> existing project I want to convert to use SQLXML with .NET
>>>> serialization which depends hugely on identity values... will this be
>>>> improved in future. I suppose you can always write XSLT to generate
>>>> clever SQL script or something as a work-around, but I like the
>>>> convenience of updategrams. Let MS do the query generation work :).
>>>
>>> I will pass this along.
>>>
>>>> Question 4:
>>>> I know AS in 2005 uses XML/A. What is the chances in future of mapping
>>>> XSD to cubes instead of using XSLT to get the same effect? I don't know
>>>> if this is the right group to pose this question though.
>>>
>>> You better suggest that in the AS newsgroup (although I will forward
>>> this request/question).
>>>
>>>> Question 5:
>>>> SQL queries FOR XML doesn't support XML Attribute Groups, will this be
>>>> supported in future? Or is a XSLT the only way to do this as well?
>>>
>>> I don't quite understand this question. Attribute Groups are an XML
>>> schema concept.
>>> Could you please provide an example?
>>>
>>> Thanks
>>> Michael
>>>
>>>>
>>>> "Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx> wrote in message
>>>> news:Ok6LcyDeFHA.640@xxxxxxxxxxxxxxxxxxxxxxx
>>>>> Hi Joe
>>>>>
>>>>> This should not be a problem. Since we do not look at the relational
>>>>> schema when we generate the queries, we do not know whether the column
>>>>> can be null or not. However, the query optimizier will know about it
>>>>> and this optimize these expressions away. Thus, there should be no
>>>>> slow down in performance due to these IS NOT NULL checks...
>>>>>
>>>>> Best regards
>>>>> Michael
>>>>>
>>>>> "Joe" <morbidcamel@xxxxxxx> wrote in message
>>>>> news:%23A08G49dFHA.2776@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>> My database I am working with is very normalized so I ended up
>>>>>> creating
>>>>>> views for certain tables which I then use in my XSD schema to select
>>>>>> XML
>>>>>> from the DB.
>>>>>>
>>>>>> The problem is the generated query does unecessary existence checks
>>>>>> because
>>>>>> it doesn't read the underlying table column schema info.. the
>>>>>> underlying
>>>>>> column is definately marked "NOT NULL".
>>>>>> ...
>>>>>> (((((_Q6.A32 IS NOT NULL AND (_Q6.A32 =
>>>>>> N'11111145-110b-11c4-d8b3-c8c5154c131e') OR _Q6.A32 IS NOT NULL AND
>>>>>> (_Q6.A32
>>>>>> = N'11111145-110b-11c4-d8b3-c8c5154c131d')) OR _Q6.A32 IS NOT NULL
>>>>>> ...
>>>>>>
>>>>>> How do I get rid of this unecessary checks. Surely it slows down
>>>>>> performance
>>>>>> of the query.
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: Annotated schema with views?
    ... See below for the answers that I know (I am not the owner of the SQLXML ... > FOR XML PATH instead of the bulky FOR XML EXPLICIT. ... > I'm running into limitations in terms of performace with my XSD Schema ... > query elements in fragments and add children manually. ...
    (microsoft.public.sqlserver.xml)
  • Re: Bug in SQLXML
    ... If so I can file a bug. ... Program Manager - SqlXml ... > with "FOR XML NESTED" clause. ...
    (microsoft.public.sqlserver.xml)
  • Re: Annotated schema with views?
    ... I have attached some XML sample. ... I cannot give you the schema because there ... Can SQLXML annotations added to this attribute group be interpreted by the ... > interpreted as complex type elements so I would recommend using attributes ...
    (microsoft.public.sqlserver.xml)
  • wp-02-0007: Microsoft SQLXML ISAPI Overflow and Cross Site Scripting
    ... Westpoint Security Advisory ... SQLXML allows XML data to be transferred to and from SQL Server, ... SQlXML has two vulnerabilities: a buffer overflow in the SQLXML ISAPI ...
    (Bugtraq)
  • Re: Can DTS read a file as one big blob?
    ... Yup....the SQLXML bulk load object is part of SQLXML. ... an add-on or additional component to install. ... SQLXML Bulk Load in ActiveX scripts to import XML files into ...
    (microsoft.public.sqlserver.server)