This cast statement works on column TextNotes:
,cast(replace(cast(tblDataActions.TextNotes as nvarchar(max)),'\Par','') as ntext) As TextNotes
I'm having trouble formatting the same cast statement to work with this XML data field:
,tblDataPermit.XmlDataField.value('(//*[local-name()="Description"])[1]', 'nvarchar(max)') AS Description
Thank you for any help.
provide sample xml data
> <XmlDataField xmlns="http://MgmsSchema/Building_Permit_Detail_Entry" ApplicationNumber="00006505"><FloodPlain>False</FloodPlain><WaterFrontage>False</WaterFrontage><OccupancyUse> 1</OccupancyUse><OccupancyClass /><AlternateInsp4>False</AlternateInsp4><NoBuildings>0</NoBuildings><NoUnits>0</NoUnits><ExtraLookup1> 1</ExtraLookup1><ExtraNumber1>0</ExtraNumber1><OffSiteFabrication>False</OffSiteFabrication><AllowedInDistrict>False</AllowedInDistrict><SmokeDetector>False</SmokeDetector><SmokeDetReqd>False</SmokeDetReqd><CurbCut>False</CurbCut><DrivewayPermit>False</DrivewayPermit><NoRooms>0</NoRooms><NoBaths>0</NoBaths><NoHalfBaths>0</NoHalfBaths><NoBedrooms>0</NoBedrooms><BuildingType> 1</BuildingType><CoveragePct /><ProjectType> 18</ProjectType><ConstructionType> 1</ConstructionType><Description>{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Microsoft Sans Serif;}}
> \viewkind4\uc1\pard\f0\fs20 Kitchen remodel and installation new exterior door in dining room.\fs17\par
> }
> </Description><NoStories>0</NoStories><LotSize /><RoadFrontage /><LeftSideLine /><RightSideLine /><RearLine /><FrontSetback /><RearSetback /><SideYardAggregate /><LeftSetback /><RightSetback /><CornerLot>False</CornerLot><CornerLotSetBack /><AccBuildSideSetback /><AccBuildRearSetback /><StreamSetback /><ConstructionClass /><BldgWidth /><OccupancyUseCurrent /><OccupancyUseProposed /><BldgLength /><Sketch>False</Sketch><SitePlan>False</SitePlan><PlanFileNo /><BldgHeight /><FloodPlainZone /><SquareFootage>110</SquareFootage><PlansFiled>False</PlansFiled><ExtraDate1>12/30/1899 12:00:00 AM</ExtraDate1><FooterInsp>False</FooterInsp><RoughFrameInsp>True</RoughFrameInsp><PlumbingInsp>True</PlumbingInsp><AlternateInsp3>True</AlternateInsp3><FoundationInsp>False</FoundationInsp><ChimneyInsp>True</ChimneyInsp><InsulationInsp>True</InsulationInsp><AlternateInsp5>False</AlternateInsp5><AlternateInsp6>False</AlternateInsp6><OtherInsp /><Relation /><FootingMaterial /><FootingWidth /><FootingHeight /><FoundationMaterial /><FoundationHeight /><FoundationThickness /><BasementMaterial /><BasementReinforcement /><BasementThickness /><CarryGirderMaterial /><CarryGirderSize /><ColumnsMaterial /><ColumnsHeight /><ColumnsDistanceApart /><ChimneyMaterial /><ChimneyNoFlues>0</ChimneyNoFlues><ChimneyFireplaces>0</ChimneyFireplaces><ChimneyHearths>0</ChimneyHearths><FloorJoistMaterial /><FloorJoistSize /><FloorJoistOC /><FlooringSubfloor /><FlooringFinish /><ExteriorMaterial /><ExteriorSize /><ExteriorSheathing /><ExteriorSiding /><InteriorMaterial /><InteriorSize /><InteriorSheathing /><CeilingMaterial /><CeilingSize /><CeilingOC /><RafterMaterial /><RafterSize /><RafterOC /><RafterPitch /><WaterClosets /><BathTubs /><Lavatorys /><WaterHeaters /><Showers /><Sinks /><GarbageDisp /><PorchWidth /><PorchLength /><DeckWidth /><DeckLength /><PoolWidth /><PoolLength /><InGroundPool>False</InGroundPool><GarageWidth /><GarageLength /><GarageType /><HeatSystemType /><AirConditioning>False</AirConditioning><WaterSource /><SewageDisposalType /><GasType /><ElecSvcAmps /><NoElevators /><NoGarages /><NoWindows /><NoFireplaces /><NoEnclosedParking /><NoOutsideParking /><ParkingAreaSqFt /><LivingAreaSqFt /><BasementAreaSqFt /><GarageAreaSqFt /><OfficeSalesSqFt /><ServiceSqFt /><ManufacturingSqFt /><EstStartDate>12/30/1899 12:00:00 AM</EstStartDate><EstFinishDate>12/30/1899 12:00:00 AM</EstFinishDate><Notes2 /><UserText1 /><UserText2 /><UserDate1>12/30/1899 12:00:00 AM</UserDate1><UserDate2>12/30/1899 12:00:00 AM</UserDate2><UserCurrency1>0</UserCurrency1><UserCurrency2>0</UserCurrency2><UserNumeric1>0</UserNumeric1><UserNumeric2>0</UserNumeric2><UserYesNo1>False</UserYesNo1><UserYesNo2>False</UserYesNo2><OccupancyType /><FinalInsp>True</FinalInsp><FloodPlainInfo /><ExtraMemo1 /><ExtraMemo2 /><ExtraMemo3 /><ExtraMemo4 /><Extramemo5 /><ExtraMemo6 /><ExtraMemo7 /><ExtraMemo8 /><ExtraMemo9 /><ExtraMemo10 /><ExtraText1 /><AlternateInsp1>True</AlternateInsp1><ExtraText3 /><ExtraText4 /><ExtraText5 /><ExtraText6 /><ExtraText7 /><ExtraText8 /><ExtraText9 /><ExtraText10 /><ExtraYesNo1>False</ExtraYesNo1><ExtraYesNo2>False</ExtraYesNo2><ExtraYesNo3>False</ExtraYesNo3><ExtraYesNo4>False</ExtraYesNo4><ExtraYesNo5>False</ExtraYesNo5><AlternateInsp2>True</AlternateInsp2><ExtraLookup2 /><ExtraLookup3 /><ExtraLookup4 /><ExtraLookup5 /><ExtraCurrency1>0</ExtraCurrency1><ExtraCurrency2>0</ExtraCurrency2><ExtraCurrency3>0</ExtraCurrency3><ExtraCurrency4>0</ExtraCurrency4><ExtraCurrency5>0</ExtraCurrency5><ResidenceType /><ExtraNumber2>0</ExtraNumber2><ExtraNumber3>0</ExtraNumber3><ExtraNumber4>0</ExtraNumber4><ExtraNumber5>0</ExtraNumber5><ExtraText2 /><ExtraDate2>12/30/1899 12:00:00 AM</ExtraDate2><ExtraDate3>12/30/1899 12:00:00 AM</ExtraDate3><ExtraDate4>12/30/1899 12:00:00 AM</ExtraDate4><ExtraDate5>12/30/1899 12:00:00 AM</ExtraDate5></XmlDataField>thanks. on table tblDataPermit what data type is column XmlDataField. What error are you getting when doing the cast ?
Incorrect syntax near cast, expected AS
I am not seeing a cast on the above