CREATE TABLE [dbo].[T1]( [IntCol] [int] IDENTITY(1,1) NOT NULL, [XmlCol] [xml] NULL)
INSERT INTO T1(XmlCol) SELECT * FROM OPENROWSET(BULK 'C:\work1\doc8652442.xml', SINGLE_BLOB) AS x;
DECLARE @Xdoc xml; SET @Xdoc = (select [XmlCol] FROM [test1].[dbo].[T1]); WITH XMLNAMESPACES (DEFAULT 'urn://x-artefacts-rosreestr-ru/outgoing/kpt/10.0.1', 'urn://x-artefacts-rosreestr-ru/commons/complex-types/entity-spatial/5.0.1' as ns3) SELECT Parcel.value('@CadastralNumber', 'nvarchar(50)')as data, Ordinate.value('@X', 'nvarchar(50)') as X,Ordinate.value('@Y', 'nvarchar(50)') as Y FROM @Xdoc.nodes('//Parcel') col(Parcel) CROSS APPLY Parcel.nodes('//ns3:Ordinate') tab(Ordinate)
CREATE FUNCTION [dbo].[SpatialElementToLineString](@wXml xml) RETURNS geometry AS BEGIN DECLARE @BuildString NVARCHAR(MAX); WITH XMLNAMESPACES ('urn://x-artefacts-rosreestr-ru/commons/complex-types/entity-spatial/5.0.1' as ns3) SELECT @BuildString = COALESCE(@BuildString + ',', '') + Ordinate.value('@Y', 'NVARCHAR(50)') + ' ' + Ordinate.value('@X', 'NVARCHAR(50)') FROM @wXml.nodes('//ns3:Ordinate') col(Ordinate); SET @BuildString = 'LineString(' + @BuildString + ')'; return geometry::STGeomFromText(@BuildString, 0); END
CREATE FUNCTION [dbo].[ParcelToLineString](@Xdoc xml) RETURNS @Tbl TABLE ( CadastralNumber nvarchar(max), Geom geometry ) AS begin WITH XMLNAMESPACES (DEFAULT 'urn://x-artefacts-rosreestr-ru/outgoing/kpt/10.0.1', 'urn://x-artefacts-rosreestr-ru/commons/complex-types/entity-spatial/5.0.1' as ns3) insert into @Tbl(CadastralNumber, Geom) SELECT @Xdoc.value('/*[1]/@CadastralNumber', 'nvarchar(max)') as CadastralNumber, [dbo].[SpatialElementToLineString](Parcel.query('.')) as geom FROM @Xdoc.nodes('//ns3:SpatialElement') col(Parcel); RETURN; end
CREATE TABLE [dbo].[CadastrTbl]( [id] [int] IDENTITY(1,1) NOT NULL, [CadastralNumber] [nvarchar](255) NULL, [geom] [geometry] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
DECLARE @Xdoc xml; SET @Xdoc = (select [XmlCol] FROM [test1].[dbo].[T1]); DECLARE @CURSOR CURSOR SET @CURSOR = CURSOR SCROLL FOR WITH XMLNAMESPACES (DEFAULT 'urn://x-artefacts-rosreestr-ru/outgoing/kpt/10.0.1') SELECT Parcel.query('.') FROM @Xdoc.nodes('//Parcel') col(Parcel); DECLARE @Parcel xml; OPEN @CURSOR FETCH NEXT FROM @CURSOR INTO @Parcel WHILE @@FETCH_STATUS = 0 BEGIN insert into [test1].[dbo].[CadastrTbl]([CadastralNumber],[geom]) select * from dbo.ParcelToLineString(@Parcel); FETCH NEXT FROM @CURSOR INTO @Parcel END CLOSE @CURSOR
Source: https://habr.com/ru/post/307294/
All Articles