Zine.net online

Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc
w Szukaj

dev2dev

XSD(SQL) = XML (valid!)

Często stajemy przed problemem wykonania zapytania SQL, które wygeneruje zgodny z założonym XSD wynik typu XML. Można to zrobić na kilka sposobów:

  • Wygenerować wynik zapytania, w aplikacji .NET zastosować klasę XmlReaderSettings, ustawiając stosowne właściwości: ValidationType (na ValidationType.Schema), Schemas (podając gdzie znajduje się schema do dokumentu XML) oraz ustawiając ValidationEventHandler, który będzie przechwytywał wszystkie komunikaty błędów walidacji. Metoda ta ma zasadniczą pozytywna cechę, że pozwala na wykrycie wszystkich błędów walidacji dokumentu XML.

            XmlReaderSettings xrs = new XmlReaderSettings();
            xrs.ValidationType = ValidationType.Schema;
            xrs.ValidationEventHandler += new ValidationEventHandler(xrs_ValidationEventHandler);

            xrs.Schemas = Schemas.Instance;

            UTF8Encoding utf8 = new UTF8Encoding();

            XmlTextReader xr = new XmlTextReader(new MemoryStream(utf8.GetBytes(xmlMessage)));
            XmlReader xvr = XmlReader.Create(xr, xrs);
            xml = new XmlDocument();
            xml.Load(xvr);

  • W kodzie T-SQL zastosować zmienną tablicową z kolumną typu XML i założoną dla niej schemą. Najpierw należy zarejestrować stosowną schema. Na przykład taką:

CREATE XML SCHEMA COLLECTION moja_schema AS ‘<xs:schema xmlns:sql="urn:schemas-microsoft-com:mapping-schema" attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="AuthorsXMLNew">
    <xs:complexType>
      <xs:sequence>
        <xs:element sql:field="au_id" name="au_id" type="xs:string" />
        <xs:element sql:field="au_lname" name="au_lname" type="xs:string" />
        <xs:element sql:field="au_fname" name="au_fname" type="xs:string" />
        <xs:element sql:field="phone" name="phone" type="xs:string" />
        <xs:element sql:field="address" name="address" type="xs:string" />
        <xs:element sql:field="city" name="city" type="xs:string" />
        <xs:element sql:field="state" name="state" type="xs:string" />
        <xs:element sql:field="zip" name="zip" type="xs:unsignedInt" />
        <xs:element sql:field="contract" sql:datatype="bit" name="contract" type="xs:unsignedByte" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>’

 

a następnie stworzyć procedurę:

ALTER PROCEDURE xml_validation
    @result nvarchar(max) = NULL OUTPUT
AS
BEGIN

    BEGIN TRY
        DECLARE @x xml(moja_schema)

        SET @x = (SELECT City
        FROM Person.Address
        FOR XML PATH ('ADDRESS'), TYPE)

    END TRY
    BEGIN CATCH
        SET @result = ERROR_MESSAGE()
    END CATCH

END

 

Po wykonaniu której otrzymamy wynik:

XML Validation: Declaration not found for element 'ADDRESS'. Location: /*:ADDRESS[1]

 

Słabą stroną tego rozwiązania jest fakt, że walidacja kończy sie wraz z pierwszym błędem.

  • Zastosować SQLXML z XPath. I to rozwiązanie jest najciekawsze (chociaż niestety nie jest pozbawione ograniczeń). Przede wszystkim dokument XSD dla rezultatu XML powinien spełniać wymagania mapowania zawarte w namespace urn:schemas-microsoft-com:mapping-schema. Poniższy przykład jest wzięty  książki Pro SQL Server 2008 XML, Michael Coles, Apress 2008, i należy go zastosować do bazy AdventureWorks.

 

<?xml version="1.0" encoding="utf-16" ?>
<xsd:schema xmlns:msdata="urn:schemas-microsoft-com:mapping-schema" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:annotation>
    <xsd:appinfo>
<msdata:relationship name="Customer-Person" parent="Sales.Customer" parent-key="PersonID" child="Person.Person" child-key="BusinessEntityID" />
<msdata:relationship name="Customer-SalesOrderHeader" parent="Sales.Customer" parent-key="CustomerID" child="Sales.SalesOrderHeader" child-key="CustomerID" />
</xsd:appinfo>
  </xsd:annotation>
  <xsd:element msdata:relation="Sales.Customer" msdata:key-fields="CustomerID" name="Customer" type="CustomerType" />
  <xsd:complexType name="CustomerType">
    <xsd:sequence>
      <xsd:element msdata:relation="Person.Person" msdata:key-fields="BusinessEntityID" msdata:relationship="Customer-Person" name="Person">
        <xsd:complexType>
          <xsd:attribute name="LastName" type="xsd:string" />
          <xsd:attribute name="MiddleName" type="xsd:string" />
          <xsd:attribute name="FirstName" type="xsd:string" />
        </xsd:complexType>
      </xsd:element>
      <xsd:element msdata:relation="Sales.SalesOrderHeader" msdata:key-fields="SalesOrderID" msdata:relationship="Customer-SalesOrderHeader" name="SalesOrderHeader">
        <xsd:complexType>
          <xsd:attribute name="SalesOrderId" type="xsd:integer" />
          <xsd:attribute name="SubTotal" type="xsd:decimal" />
          <xsd:attribute name="TotalDue" type="xsd:decimal" />
          <xsd:attribute name="OrderDate" type="xsd:date" />
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
    <xsd:attribute name="CustomerID" type="xsd:integer" />
  </xsd:complexType>
</xsd:schema>

 

I teraz wystarczy mały fragment kodu w C#:

string result = string.Empty;

SqlXmlCommand sqlcom = new
            SqlXmlCommand("PROVIDER=SQLOLEDB;SERVER=moj_server;" +
            "INITIAL CATALOG=AdventureWorks;INTEGRATED SECURITY=SSPI;");
            sqlcom.CommandText = moje_xpath_query;
            sqlcom.CommandType = SqlXmlCommandType.XPath;
            sqlcom.SchemaPath = "C:\\moje_xsd\\moja_jakas_schema.xsd";
            Stream s = sqlcom.ExecuteStream();
            StreamReader sr = new StreamReader(s);
            result = sr.ReadToEnd();

 

I w zmiennej result mamy gotowy i zwalidowany(!) z założonym dokumentem XSD dokument XML. Jeżeli tylko będziemy przestrzegać ograniczeń SQLXML to mamy gotową “maszynkę” do generowania dokumentów XML. Gdy dokument XSD zmieni się, to wystarczy skopiować go odpowiedniego katalogu i za chwilę mamy gotowe rozwiązanie.

Opublikowane 4 marca 2009 21:15 przez marekpow
Filed under: , ,

Komentarze:

Brak komentarzy
Komentarze anonimowe wyłączone
W oparciu o Community Server (Personal Edition), Telligent Systems