Witam,
Mam do napisania procedurę w SQL która przyjmuję jako argumenty ścieżki do dwóch plików i porównuje ich strukturę czyli ilość kolumny wierszy itp są to pliki głównie XML a dokładniej raporty ETL. Nie mam pojęcia jak to ugryźć czy w samym SQL da się to zrobić czy trzeba skorzystać ze skryptu np w C#?
Kiedyś coś podobnego potrzebowałem i gdzieś na googlach znalazłem takie cudeńko. Nie pamiętam czy to działa czy nie ale możesz to sobie sam zweryfikować:
CREATE FUNCTION [dbo].[CompareXml]
(
@xml1 XML,
@xml2 XML
)
RETURNS INT
AS
BEGIN
DECLARE @ret INT
SELECT @ret = 0
-- -------------------------------------------------------------
-- If one of the arguments is NULL then we assume that they are
-- not equal.
-- -------------------------------------------------------------
IF @xml1 IS NULL OR @xml2 IS NULL
BEGIN
RETURN 1
END
-- -------------------------------------------------------------
-- Match the name of the elements
-- -------------------------------------------------------------
IF (SELECT @xml1.value('(local-name((/*)[1]))','VARCHAR(MAX)'))
<>
(SELECT @xml2.value('(local-name((/*)[1]))','VARCHAR(MAX)'))
BEGIN
RETURN 1
END
---------------------------------------------------------------
--Match the value of the elements
---------------------------------------------------------------
IF((@xml1.query('count(/*)').value('.','INT') = 1) AND (@xml2.query('count(/*)').value('.','INT') = 1))
BEGIN
DECLARE @elValue1 VARCHAR(MAX), @elValue2 VARCHAR(MAX)
SELECT
@elValue1 = @xml1.value('((/*)[1])','VARCHAR(MAX)'),
@elValue2 = @xml2.value('((/*)[1])','VARCHAR(MAX)')
IF @elValue1 <> @elValue2
BEGIN
RETURN 1
END
END
-- -------------------------------------------------------------
-- Match the number of attributes
-- -------------------------------------------------------------
DECLARE @attCnt1 INT, @attCnt2 INT
SELECT
@attCnt1 = @xml1.query('count(/*/@*)').value('.','INT'),
@attCnt2 = @xml2.query('count(/*/@*)').value('.','INT')
IF @attCnt1 <> @attCnt2 BEGIN
RETURN 1
END
-- -------------------------------------------------------------
-- Match the attributes of attributes
-- Here we need to run a loop over each attribute in the
-- first XML element and see if the same attribut exists
-- in the second element. If the attribute exists, we
-- need to check if the value is the same.
-- -------------------------------------------------------------
DECLARE @cnt INT, @cnt2 INT
DECLARE @attName VARCHAR(MAX)
DECLARE @attValue VARCHAR(MAX)
SELECT @cnt = 1
WHILE @cnt <= @attCnt1
BEGIN
SELECT @attName = NULL, @attValue = NULL
SELECT
@attName = @xml1.value(
'local-name((/*/@*[sql:variable("@cnt")])[1])',
'varchar(MAX)'),
@attValue = @xml1.value(
'(/*/@*[sql:variable("@cnt")])[1]',
'varchar(MAX)')
-- check if the attribute exists in the other XML document
IF @xml2.exist(
'(/*/@*[local-name()=sql:variable("@attName")])[1]'
) = 0
BEGIN
RETURN 1
END
IF @xml2.value(
'(/*/@*[local-name()=sql:variable("@attName")])[1]',
'varchar(MAX)')
<>
@attValue
BEGIN
RETURN 1
END
SELECT @cnt = @cnt + 1
END
-- -------------------------------------------------------------
-- Match the number of child elements
-- -------------------------------------------------------------
DECLARE @elCnt1 INT, @elCnt2 INT
SELECT
@elCnt1 = @xml1.query('count(/*/*)').value('.','INT'),
@elCnt2 = @xml2.query('count(/*/*)').value('.','INT')
IF @elCnt1 <> @elCnt2
BEGIN
RETURN 1
END
-- -------------------------------------------------------------
-- Start recursion for each child element
-- -------------------------------------------------------------
SELECT @cnt = 1
SELECT @cnt2 = 1
DECLARE @x1 XML, @x2 XML
DECLARE @noMatch INT
WHILE @cnt <= @elCnt1
BEGIN
SELECT @x1 = @xml1.query('/*/*[sql:variable("@cnt")]')
--RETURN CONVERT(VARCHAR(MAX),@x1)
WHILE @cnt2 <= @elCnt2
BEGIN
SELECT @x2 = @xml2.query('/*/*[sql:variable("@cnt2")]')
SELECT @noMatch = dbo.CompareXml( @x1, @x2 )
IF @noMatch = 0 BREAK
SELECT @cnt2 = @cnt2 + 1
END
SELECT @cnt2 = 1
IF @noMatch = 1
BEGIN
RETURN 1
END
SELECT @cnt = @cnt + 1
END
RETURN @ret
END
Próbuję rozkminić to co wysłałeś ale niestety chyba nie działa ma ktoś jeszcze jakieś pomysły?
Piszesz, że próbujesz "rozkminić" ale chyba nie działa ... co ci nie działa dokładnie?
po prostu zwraca zły wynik, np dla wywołania
EXEC @o= CompareXML '<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Dont forget me this weekend!</body>
</note>',
'<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Dont forget me this weekend!</body>
</note>'
Zwraca 0 a są one identyczne ogólnie zmieniam input lecz cały czas mam wynik 0.
No żebym gotowcami musiał rzucać bo się google używać nie umie ...
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[udf_XML_Is_Equal]
(
@Data1 xml,
@Data2 xml
)
returns bit
as
begin
declare
@i bigint, @cnt1 bigint, @cnt2 bigint,
@Sub_Data1 xml, @Sub_Data2 xml,
@Name varchar(max), @Value1 nvarchar(max), @Value2 nvarchar(max)
if @Data1 is null or @Data2 is null
return 1
--=========================================================================================================
-- If more than one root - recurse for each element
--=========================================================================================================
select
@cnt1 = @Data1.query('count(/*)').value('.','int'),
@cnt2 = @Data1.query('count(/*)').value('.','int')
if @cnt1 <> @cnt2
return 0
if @cnt1 > 1
begin
select @i = 1
while @i <= @cnt1
begin
select
@Sub_Data1 = @Data1.query('/*[sql:variable("@i")]'),
@Sub_Data2 = @Data2.query('/*[sql:variable("@i")]')
if dbo.udf_XML_Is_Equal_New(@Sub_Data1, @Sub_Data2) = 0
return 0
select @i = @i + 1
end
return 1
end
--=========================================================================================================
-- Comparing root data
--=========================================================================================================
if @Data1.value('local-name(/*[1])','nvarchar(max)') <> @Data2.value('local-name(/*[1])','nvarchar(max)')
return 0
if @Data1.value('/*[1]', 'nvarchar(max)') <> @Data2.value('/*[1]', 'nvarchar(max)')
return 0
--=========================================================================================================
-- Comparing attributes
--=========================================================================================================
select
@cnt1 = @Data1.query('count(/*[1]/@*)').value('.','int'),
@cnt2 = @Data1.query('count(/*[1]/@*)').value('.','int')
if @cnt1 <> @cnt2
return 0
if exists (
select *
from
(
select
T.C.value('local-name(.)', 'nvarchar(max)') as Name,
T.C.value('.', 'nvarchar(max)') as Value
from @Data1.nodes('/*[1]/@*') as T(C)
) as D1
full outer join
(
select
T.C.value('local-name(.)', 'nvarchar(max)') as Name,
T.C.value('.', 'nvarchar(max)') as Value
from @Data2.nodes('/*[1]/@*') as T(C)
) as D2
on D1.Name = D2.Name
where
not
(
D1.Value is null and D2.Value is null or
D1.Value is not null and D2.Value is not null and D1.Value = D2.Value
)
)
return 0
--=========================================================================================================
-- Recursively running for each child
--=========================================================================================================
select
@cnt1 = @Data1.query('count(/*[1]/*)').value('.','int'),
@cnt2 = @Data2.query('count(/*[1]/*)').value('.','int')
if @cnt1 <> @cnt2
return 0
select @i = 1
while @i <= @cnt1
begin
select
@Sub_Data1 = @Data1.query('/*/*[sql:variable("@i")]'),
@Sub_Data2 = @Data2.query('/*/*[sql:variable("@i")]')
if dbo.udf_XML_Is_Equal(@Sub_Data1, @Sub_Data2) = 0
return 0
select @i = @i + 1
end
return 1
END
declare
@b bit;
EXEC @b = udf_XML_Is_Equal '<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Dont forget me this weekend!</body>
</note>',
'<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Dont forget me this weekend!</body>
</note>'
select @b
Super dzięki działa, mam jeszcze jedno pytanie do was
Mam procedurę która sprawdza czy plik istnieje i mam przypadek iż plik jest bardzo duży i jest w trakcie kopiowania wtedy gdy wywołuje procedurę jak sprawdzi status pliku? jest to plik w formacie xml.