Querying XML element values
Lonedog
--First the Schema Collection and table definition
CREATE XML SCHEMA COLLECTION [dbo].[VALIDXML] AS
'<xsd:schema xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
attributeFormDefault="unqualified" elementFormDefault="qualified"
targetNamespace="http://schemas.xmlsoap.org/soap/envelope/">
<xs:import namespace="https://validsys.net/APIPOS" />
<xs:element name="VAP_VALIDCustomerConfirmPOS">
<xs:complexType>
<xs:sequence>
<xs:element name="Body">
<xs:complexType>
<xs:sequence>
<xs:element xmlns:q1="https://validsys.net/APIPOS"
ref="q1:VALIDCustomerConfirmPOS" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xsd:schema>
<xs:schema xmlns:tns="https://validsys.net/APIPOS"
attributeFormDefault="unqualified" elementFormDefault="qualified"
targetNamespace="https://validsys.net/APIPOS"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="VALIDCustomerConfirmPOS">
<xs:complexType>
<xs:sequence>
<xs:element name="userName" type="xs:string" nillable="true" />
<xs:element name="password" type="xs:string" nillable="true" />
<xs:element name="requestID" type="xs:string" nillable="true" />
<xs:element name="locationID" type="xs:string" nillable="true" />
<xs:element name="transactionID" type="xs:string" nillable="true" />
<xs:element name="checkAmount" type="xs:string" nillable="true" />
<xs:element name="checkFeeAmount" type="xs:string" nillable="true" />
<xs:element name="checkPayoutAmount" type="xs:string" nillable="true" />
<xs:element name="payoutType" type="xs:string" nillable="true" />
<xs:element name="cardNumber" type="xs:string" nillable="true" />
<xs:element name="cardPIN" type="xs:string" nillable="true" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
'
/****** Object: Table [dbo].[XmlLog] Script Date: 10/27/2009 11:55:16
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[XmlLog](
[XmlLogId] [int] IDENTITY(1,1) NOT NULL,
[ApplicationLogPointXrefId] [int] NOT NULL,
[Information] [xml](CONTENT [dbo].[VALIDXML]) NULL,
[ReferenceIP] [nvarchar](50) NULL,
[LevelId] [int] NOT NULL CONSTRAINT [dfXmlLog_LevelId] DEFAULT ((1)),
[StatusId] [int] NOT NULL,
[RecordGUID] [uniqueidentifier] NOT NULL CONSTRAINT [dfXmlLog_RecordGUID]
DEFAULT (newid()),
CONSTRAINT [pkXmlLog] PRIMARY KEY CLUSTERED
(
[XmlLogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]
--After inserting some xml into the table (no errors for an insert of 1800+
records)....
SELECT Information.query('declare namespace
a="https://validsys.net/APIPOS"; //a:VALIDCustomerConfirmPOS/a:requestID[1]')
FROM XmlLog
WHERE Information.exist('declare namespace a="https://validsys.net/APIPOS";
//a:VALIDCustomerConfirmPOS') = 1
--works correctly
--But I can't get this to work
SELECT Information.value('declare namespace
a="https://validsys.net/APIPOS";
(//a:VALIDCustomerConfirmPOS/a:requestID)[1]','nvarchar(255)')
FROM XmlLog
WHERE Information.exist('declare namespace a="https://validsys.net/APIPOS";
//a:VALIDCustomerConfirmPOS') = 1
--the error is
--Msg 9314, Level 16, State 1, Line 1
--XQuery [XmlLog.Information.value()]: Cannot implicitly atomize or apply
'fn:data()'
--to complex content elements, found type 'xs:anyType' within inferred type
--'(element(a{https://validsys.net/APIPOS}:requestID,xs:string) |
element(a{https://validsys.net/APIPOS}:requestID,xs:anyType)) ?'.
--What would be the reason, and what is an alternative to retrieve the value
of the element?
Bob
Please post a small piece of sample XML and expected results.