Validação xsd/xml Tsql

[alfinete]

estou a tentar validar uma string xml atraves de um schema xml em Tdsql.

Criação do schema com multimplos Namespaces
___________________

CREATE XML SCHEMA COLLECTION [dbo].[XML_REQUEST_GENERIC_SCHEMA] as '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  targetNamespace="http://Xml.Request.Total.com"
  xmlns:ns="http://Xml.Request.Total.com">
   <xsd:element name="root"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="body"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Request"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="BodyRawPrint"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="receiptData"><xsd:complexType><xsd:simpleContent><xsd:extension base="xsd:string"><xsd:attribute name="body" type="xsd:string" /></xsd:extension></xsd:simpleContent></xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="copies" type="xsd:byte" /></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element><xsd:element name="BodyRawPayment"><xsd:complexType><xsd:simpleContent><xsd:extension base="xsd:string"><xsd:attribute name="amount" type="xsd:byte" /><xsd:attribute name="entity" type="xsd:string" /><xsd:attribute name="printLocally" type="xsd:string" /><xsd:attribute name="reference" type="xsd:int" /></xsd:extension></xsd:simpleContent></xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="RequestType" type="xsd:string" /><xsd:attribute name="ID" type="xsd:byte" /></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element><xsd:element name="header"><xsd:complexType><xsd:simpleContent><xsd:extension base="xsd:string"><xsd:attribute name="EquipementId" type="xsd:byte" /><xsd:attribute name="Userlogin" type="xsd:byte" /><xsd:attribute name="Password" type="xsd:byte" /><xsd:attribute name="SystemId" type="xsd:byte" /></xsd:extension></xsd:simpleContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element>
   </xsd:schema>'
GO

alter XML SCHEMA COLLECTION [dbo].[XML_REQUEST_GENERIC_SCHEMA] add '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  targetNamespace="http://Xml.Request.BodyPaymentPrint.com"
  xmlns:ns="http://Xml.Request.BodyPaymentPrint.com">
   <xsd:element name="root"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="body"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Request"><xsd:complexType><xsd:simpleContent><xsd:extension base="xsd:string"><xsd:attribute name="RequestType" type="xsd:string" /><xsd:attribute name="BodyRawPayment" type="xsd:string" /><xsd:attribute name="BodyRawPrint" type="xsd:string" /><xsd:attribute name="ID" type="xsd:byte" /></xsd:extension></xsd:simpleContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element><xsd:element name="header"><xsd:complexType><xsd:simpleContent><xsd:extension base="xsd:string"><xsd:attribute name="EquipementId" type="xsd:byte" /><xsd:attribute name="Userlogin" type="xsd:byte" /><xsd:attribute name="Password" type="xsd:byte" /><xsd:attribute name="SystemId" type="xsd:byte" /></xsd:extension></xsd:simpleContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element>
   </xsd:schema>'
GO

alter XML SCHEMA COLLECTION [dbo].[XML_REQUEST_GENERIC_SCHEMA] add '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  targetNamespace="http://Xml.Request.BodyPayment.com"
  xmlns:ns="http://Xml.Request.BodyPayment.com">
   <xsd:element name="root"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="body"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Request"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="BodyRawPrint"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="receiptData"><xsd:complexType><xsd:simpleContent><xsd:extension base="xsd:string"><xsd:attribute name="body" type="xsd:string" /></xsd:extension></xsd:simpleContent></xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="copies" type="xsd:byte" /></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="RequestType" type="xsd:string" /><xsd:attribute name="BodyRawPayment" type="xsd:string" /><xsd:attribute name="ID" type="xsd:byte" /></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element><xsd:element name="header"><xsd:complexType><xsd:simpleContent><xsd:extension base="xsd:string"><xsd:attribute name="EquipementId" type="xsd:byte" /><xsd:attribute name="Userlogin" type="xsd:byte" /><xsd:attribute name="Password" type="xsd:byte" /><xsd:attribute name="SystemId" type="xsd:byte" /></xsd:extension></xsd:simpleContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element>
   </xsd:schema>'
GO

alter XML SCHEMA COLLECTION [dbo].[XML_REQUEST_GENERIC_SCHEMA] add '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  targetNamespace="http://Xml.Request.BodyPrint.null.com"
  xmlns:ns="http://Xml.Request.BodyPrint.com">
   <xsd:element name="root"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="body"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="Request"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="BodyRawPayment"><xsd:complexType><xsd:simpleContent><xsd:extension base="xsd:string"><xsd:attribute name="amount" type="xsd:byte" /><xsd:attribute name="entity" type="xsd:string" /><xsd:attribute name="printLocally" type="xsd:string" /><xsd:attribute name="reference" type="xsd:int" /></xsd:extension></xsd:simpleContent></xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="RequestType" type="xsd:string" /><xsd:attribute name="BodyRawPrint" type="xsd:string" /><xsd:attribute name="ID" type="xsd:byte" /></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element><xsd:element name="header"><xsd:complexType><xsd:simpleContent><xsd:extension base="xsd:string"><xsd:attribute name="EquipementId" type="xsd:byte" /><xsd:attribute name="Userlogin" type="xsd:byte" /><xsd:attribute name="Password" type="xsd:byte" /><xsd:attribute name="SystemId" type="xsd:byte" /></xsd:extension></xsd:simpleContent></xsd:complexType></xsd:element></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element>
   </xsd:schema>'
GO

Chamda de um schema apenas com um namespace
________________________________________________________________________

DECLARE @TYPED_XML_REQUEST_GENERIC_SCHEMA XML(dbo.XML_REQUEST_GENERIC_SCHEMA);

ex's de xml a validar
________________________________

DECLARE @UNTYPED_XML XML = '
<root>
  <body>
  <Request RequestType="PaymentWithReference" BodyRawPayment="null" BodyRawPrint="null" ID="12">
   
  </Request>
  </body>
  <header EquipementId="2" Userlogin="1" Password="1" SystemId="3" />
</root>
'

_____________

DECLARE @UNTYPED_XML XML =
'
<root>
  <body>
  <Request RequestType="PaymentWithReference" BodyRawPayment="null" ID="12">
  <BodyRawPrint copies="1">
  <receiptData body="alo comadre" />
  </BodyRawPrint>
  </Request>
  </body>
  <header EquipementId="2" Userlogin="1" Password="1" SystemId="3" />
</root>
'

_____________

DECLARE @UNTYPED_XML XML = '
<root>
  <body>
  <Request RequestType="PaymentWithReference" BodyRawPrint="null" ID="12">
  <BodyRawPayment amount="5" entity="Ent" printLocally="true" reference="123456789" />
  </Request>
  </body>
  <header EquipementId="2" Userlogin="1" Password="1" SystemId="3" />
</root>'

______________

DECLARE @UNTYPED_XML XML =
'<root>
  <body>
  <Request RequestType="PaymentWithReference" ID="12">
  <BodyRawPrint copies="1">
  <receiptData body="alo comadre" />
  </BodyRawPrint>
  <BodyRawPayment amount="5" entity="Ent" printLocally="true" reference="123456789" />
  </Request>
   
  </body>
  <header EquipementId="2" Userlogin="1" Password="1" SystemId="3" />
</root>';

Validação de um schema com um name space
________________________________________________________

BEGIN TRY
        SELECT @TYPED_XML_REQUEST_GENERIC_SCHEMA = @UNTYPED_XML ;
       
        SELECT MSG = N'PASSED SCHEMA VALIDATION';
END TRY
BEGIN CATCH
  SELECT MSG=ERROR_MESSAGE();
END CATCH

no meu xml schema tenho a validação para cada um dos casos em "ex's de xml a validar "

se eu fizer um schema para cada um deles e os validar com o exemplo de validação que mencionei ele funciona, se os validar com o schema com varios name spaces ele não funciona.

gostria de saber como posso fazer esta validação com um xml com multiplos namespaces

Obrigado

 

[alfinete]

Resolução
Criei um Xsd só com um schema que valida os casos todos do exemplos a validar.

e utilizei a validação referente a "Validação de um schema com um name space"

 

[alfinete]

tenho mais uma questão .

tenho um select que me devolve varios Xml

como posso validar os mesmos com o try catch do exemplo referente a "Validação de um schema com um name space"

sem ter de utilizar um ciclo , ou cursor?

obrigado