Skip to content

hiding-elements-and-attributes-by-using-sql-hide.md

Latest commit

 

History

History
143 lines (120 loc) · 6.91 KB

hiding-elements-and-attributes-by-using-sql-hide.md

File metadata and controls

143 lines (120 loc) · 6.91 KB
title Hiding Elements and Attributes by Using sql:hide
description Learn how to use the sql:hide annotation to hide elements and attributes when executing an XPath query against an XSD schema.
author MikeRayMSFT
ms.author mikeray
ms.date 03/16/2017
ms.service sql
ms.subservice xml
ms.topic reference
helpviewer_keywords
hiding elements
element mapping [SQLXML], hiding attributes and elements
hide annotation
monikerRange =azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Hiding Elements and Attributes by Using sql:hide

[!INCLUDE SQL Server Azure SQL Database] When an XPath query is executed against an XSD schema, the resulting XML document has elements and attributes that are specified in the schema. You can specify that some elements and attributes be hidden in the schema by using the sql:hide annotation. This is useful when the selection criteria of the query require particular elements or attributes in the schema, but you do not want them returned in the XML document that is generated.

The sql:hide annotation takes a Boolean value (0=false, 1=true). The acceptable values are 0, 1, true, and false.

Examples

To create working samples using the following examples, you must meet certain requirements. For more information, see Requirements for Running SQLXML Examples.

A. Specifying sql:hide on an attribute

The XSD schema in this example consists of an <Person.Contact> element with ContactID, FirstName, and LastName attributes.

The <Person.Contact> element is of complex type and, therefore, maps to the table of the same name (default mapping). All the attributes of <Person.Contact> element are of simple type and map to columns with the same names in the Person.Contactable in the AdventureWorks database. In the schema, the sql:hide annotation is specified on the ContactID attribute. When an XPath query is specified against this schema, the ContactID is not returned in the XML document.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"   
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:element name="Person.Contact" >  
     <xsd:complexType>  
       <xsd:attribute name="ContactID"  sql:hide="true" />   
       <xsd:attribute name="FirstName"   type="xsd:string" />   
       <xsd:attribute name="LastName"    type="xsd:string" />   
     </xsd:complexType>  
  </xsd:element>  
</xsd:schema>
To test a sample XPath query against the schema

  1. Copy the schema code above and paste it into a text file. Save the file as Hide.xml.

  2. Copy the following template and paste it into a text file. Save the file as HideT.xml in the same directory where you saved Hide.xml.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">  
    <sql:xpath-query mapping-schema="Hide.xml">  
        /Person.Contact[@ContactID="1"]  
    </sql:xpath-query>  
</ROOT>

    The directory path specified for the mapping schema (Hide.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:

    mapping-schema="C:\MyDir\Hide.xml"

  3. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.

    For more information, see Using ADO to Execute SQLXML 4.0 Queries.

Here is the result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">  
   <Person.Contact FirstName="Gustavo" LastName="Achong" />   
</ROOT>

When sql:hide is specified on an element, the element and its attributes or child elements do not appear in the XML document that is generated. Here is another XSD schema in which sql:hide is specified on the <OD> element:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:dt="urn:schemas-microsoft-com:datatypes"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  
  <xsd:annotation>  
    <xsd:documentation>  
      Sales.Customer-Sales.SalesOrderHeader-Sales.SalesOrderDetail Schema  
      Copyright 2004 Microsoft. All rights reserved.  
    </xsd:documentation>  
    <xsd:appinfo>  
      <sql:relationship name="CustomerOrder"  
         parent="Sales.Customer"  
                  parent-key="CustomerID"  
                  child-key="CustomerID"  
                  child="Sales.SalesOrderHeader" />  
       <sql:relationship name="OrderOrderDetails"  
                  parent="Sales.SalesOrderHeader"  
                  parent-key="SalesOrderID"  
                  child-key="SalesOrderID"  
                  child="Sales.SalesOrderDetail"/>  
    </xsd:appinfo>  
  </xsd:annotation>  
  
  <xsd:element name="Customers" sql:relation="Sales.Customer">  
    <xsd:complexType>  
      <xsd:sequence>  
        <xsd:element name="Order" sql:relation="Sales.SalesOrderHeader"   
                     maxOccurs="unbounded"   
                     sql:relationship="CustomerOrder">  
          <xsd:complexType>  
            <xsd:sequence>  
               <xsd:element name="OD" sql:relation="Sales.SalesOrderDetail"                                       maxOccurs="unbounded"                                       sql:relationship="OrderOrderDetails"                                       sql:hide="1">  
                  <xsd:complexType>  
                    <xsd:attribute name="SalesOrderID" type="xsd:string"/>  
                    <xsd:attribute name="ProductID" type="xsd:string"/>  
                  </xsd:complexType>  
               </xsd:element>  
            </xsd:sequence>  
          <xsd:attribute name="CustomerID" type="xsd:string"/>  
          <xsd:attribute name="OID" sql:field="SalesOrderID"   
                                    type="xsd:string"/>  
          <xsd:attribute name="OrderDate" type="xsd:date"/>   
        </xsd:complexType>  
      </xsd:element>  
    </xsd:sequence>  
    <xsd:attribute name="CID" sql:field="CustomerID"   
                                type="xsd:string"/>  
    </xsd:complexType>  
  </xsd:element>  
</xsd:schema>

When an XPath query (for example /Customers[@CID="1"]) is specified against this schema, the XML document that is generated does not include the <OD> element and its children, as shown in this partial result:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">  
  <Customers CID="1">  
    <Order CustomerID="1" OID="43860" OrderDate="2001-08-01" />   
    <Order CustomerID="1" OID="44501" OrderDate="2001-11-01" />   
    <Order CustomerID="1" OID="45283" OrderDate="2002-02-01" />   
    <Order CustomerID="1" OID="46042" OrderDate="2002-05-01" />   
  </Customers>  
</ROOT>