Thank you for taking time to read my post and for your expertise in this matter.
I appreciate it.
I am struggling to overcome this error when parsing typed xml attribute/value into a text
See below
IF EXISTS
(
SELECT *
FROM sys.xml_schema_collections c
JOIN sys.schemas s ON s.schema_id = c.schema_id
WHERE s.[name] = N'dbo'
AND c.[name] = N'MachineEventsSimple'
)
DROP XML SCHEMA COLLECTION dbo.MachineEventsSimple
GO
DECLARE @MachineEventsSimple nvarchar(max)
-- Assign the schema
SET @MachineEventsSimple =
N'<?xml version="1.0" encoding="utf-16"?>
<xsd:schema xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" attributeFormDefault="unqualified" elementFormDefault="qualified" version="12.5.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:import schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" />
<xsd:element name="MachineEvent">
<xsd:complexType>
<xsd:sequence>
<xsd:choice minOccurs="1" maxOccurs="1">
<xsd:element minOccurs="0" maxOccurs="1" name="SCAN">
<xsd:complexType>
<xsd:attribute name="Scan" use="required">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar">
<xsd:minLength value="1" />
<xsd:maxLength value="28" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="cptCode" use="optional">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar">
<xsd:minLength value="1" />
<xsd:maxLength value="25" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="venCode" use="optional">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar">
<xsd:minLength value="1" />
<xsd:maxLength value="25" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="LocationPath" use="optional">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar">
<xsd:minLength value="1" />
<xsd:maxLength value="100" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
<xsd:element minOccurs="0" maxOccurs="1" name="ALERT">
<xsd:complexType>
<xsd:attribute name="Message" use="required">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar">
<xsd:minLength value="1" />
<xsd:maxLength value="25" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
<xsd:element minOccurs="0" maxOccurs="1" name="INFO">
<xsd:complexType>
<xsd:anyAttribute processContents="skip" />
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:element minOccurs="0" maxOccurs="1" name="DATA">
<xsd:complexType>
<xsd:sequence>
<xsd:any minOccurs="0" processContents="skip" />
</xsd:sequence>
<xsd:anyAttribute processContents="skip" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="mcnCode" use="required">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar">
<xsd:minLength value="1" />
<xsd:maxLength value="25" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
<xsd:attribute name="CreatedOn" type="sqltypes:datetimeoffset" use="required" />
</xsd:complexType>
</xsd:element>
</xsd:schema>';
IF NOT EXISTS
(
SELECT *
FROM sys.xml_schema_collections c
JOIN sys.schemas s ON s.schema_id = c.schema_id
WHERE s.[name] = N'dbo'
AND c.[name] = N'MachineEventsSimple'
)
CREATE XML SCHEMA COLLECTION dbo.MachineEventsSimple AS @MachineEventsSimple
GO
DECLARE @MachineEvent xml
-- if the below line is enabled and the above line commented out, then I get an error
--DECLARE @MachineEvent xml (DOCUMENT dbo.MachineEventsSimple)
/*
Msg 2389, Level 16, State 1, Line 126
XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type '<anonymous> | sqltypes{http://schemas.microsoft.com/sqlserver/2004/sqltypes}:datetimeoffset | xs:boolean | xs:QName | xs:anyURI *'
*/
-- How to make the below attribute/value retrieval code work with schema bound typed variable, i.e. overcome this error? a value should be a text type
SET @MachineEvent = -- SCAN sample
N'<MachineEvent mcnCode="Holzma" CreatedOn="2021-08-05T14:33:59.484231-05:00">
<SCAN Scan="ICN114515" cptCode="Cutting" venCode="AdzicF" LocationPath="??" />
<DATA Temperature="24" WorkTime="5321" Cycles="135"/>
</MachineEvent>'
SELECT
mcnepCode = mi.c.value( 'local-name(.)', 'nvarchar(50)' )
,mcnepValue = mi.c.value( '.', 'nvarchar(4000)')
FROM @MachineEvent.nodes('MachineEvent/@*' ) mi(c)
--UNION ALL
--SELECT
-- mcnepCode = mi.c.value( 'local-name(.)', 'nvarchar(50)' )
-- ,mcnepValue = mi.c.value( '.', 'nvarchar(4000)')
--FROM @MachineEvent.nodes('MachineEvent/*[1]/@*' ) mi(c)
--UNION ALL
--SELECT
-- mcnepCode = mi.c.value( 'local-name(.)', 'nvarchar(50)' )
-- ,mcnepValue = mi.c.value( '.', 'nvarchar(4000)')
--FROM @MachineEvent.nodes('MachineEvent/DATA/@*' ) mi(c)
Thanks
Vladimir