Erhalte SQL-datasätze, die auf XML-Attributwerten basieren

Ich habe einige grundlegende XML-Abfrage mit T-SQL getan, aber ich bin nicht sicher, wie man dieses Problem angreift.

Ich möchte alle Zeilen in einer Tabelle abfragen, in der ein bestimmtes XML-Attribut einen angeforderten Wert hat.

Hier ist mein Tabellenschema:

CREATE TABLE [dbo].[Applications_Submissions] ( [Id] [int] IDENTITY(1,1) NOT NULL, [SubmissionGuid] [uniqueidentifier] NOT NULL, [TestMode] [bit] NOT NULL, [SubmissionID] [int] NOT NULL, [Status] [int] NOT NULL, [ProcessId] [int] NOT NULL, [Version] [int] NOT NULL, [ReturnUrl] [nvarchar](1000) NULL, [Applications] [xml] NOT NULL, [CreatedByUser] [int] NOT NULL, [DateCreated] [datetime] NOT NULL, [DateDeleted] [datetime] NULL, PRIMARY KEY CLUSTERED ([Id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG_Applications] ) ON [FG_Applications] TEXTIMAGE_ON [FG_Applications] 

Die in der Spalte [Application] gespeicherten XML-data sind wie folgt aufgebaut:

 <Submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Id="15" TestMode="false" SubmissionId="15" Status="0" ProcessId="0" Version="1" CreatedByUser="2" DateCreated="2016-09-28T15:08:25.667" DateDeleted="0001-01-01T00:00:00"> <ReturnUrl>/Register/RegistrationData/Register/1094/1/[SGUID]</ReturnUrl> <SubmissionGuid>f1891c84-1fda-41b4-a78b-605bfdcaf45a</SubmissionGuid> <Applications> <Application ApplicationID="15" ApplicationName="Test Form"> <FriendlyUrl>test-form-1234</FriendlyUrl> <Pages> <Page PageId="16" ApplicationId="15" ViewOrder="0"> <PageTitle>Page 1</PageTitle> <Fields> <Field FieldId="26" FieldType="10" ViewOrder="0"> <FieldTitle>Who are you</FieldTitle> <FieldValues>Jeffrey</FieldValues> <FieldsResponses /> <PossibleFieldValues /> </Field> <Field FieldId="27" FieldType="0" ViewOrder="0"> <FieldTitle>Why are you here?</FieldTitle> <FieldValues /> <FieldsResponses /> <PossibleFieldValues /> </Field> <Field FieldId="28" FieldType="5" ViewOrder="0"> <FieldTitle>Pick any</FieldTitle> <FieldsResponses> <FieldsResponse FieldResponseId="19" LinkToApplication="0" ViewOrder="0" Selected="false"> <Response>One</Response> </FieldsResponse> <FieldsResponse FieldResponseId="20" LinkToApplication="12" ViewOrder="0" Selected="false"> <Response>Two</Response> </FieldsResponse> <FieldsResponse FieldResponseId="21" LinkToApplication="0" ViewOrder="0" Selected="false"> <Response>Three</Response> </FieldsResponse> </FieldsResponses> <PossibleFieldValues /> </Field> </Fields> </Page> <Page PageId="17" ApplicationId="15" ViewOrder="0"> <PageTitle>Page 2</PageTitle> <Fields> <Field FieldId="29" FieldType="6" ViewOrder="0"> <FieldTitle>Agreement</FieldTitle> <FieldsResponses> <FieldsResponse FieldResponseId="22" LinkToApplication="16" ViewOrder="0" Selected="false"> <Response>I agree</Response> </FieldsResponse> <FieldsResponse FieldResponseId="23" LinkToApplication="16" ViewOrder="0" Selected="false"> <Response>I disagree</Response> </FieldsResponse> </FieldsResponses> <PossibleFieldValues /> </Field> </Fields> </Page> </Pages> </Application> </Applications> </Submission> 

Ich möchte die Tabellenzeilen auf der Basis des ApplicationID Attributs erhalten, das im <Application> -Knoten gespeichert ist. Jeder <Submission> kann mehrere <Application> Knoten haben.

Was ist der beste path, dies zu tun?

Ich danke dir sehr!

Das Beste dafür war xml.exist() . Aber das wird nie schnell sein … Wenn du das öfter oder mit größeren Zählungen von Zeilen brauchst, solltest du lieber die ApplicationIDs zusammen mit der eigentlichen rowID in einen Beistelltisch extrahieren. Dies kann in einem Auslöser erfolgen.

Aber das könntest du versuchen

–Mock-up Tisch

 DECLARE @Applications_Submissions TABLE(Id INT IDENTITY NOT NULL,TestText VARCHAR(MAX),Applications XML); 

– Legen Sie zwei Mock-up-Zeilen ein

 INSERT INTO @Applications_Submissions VALUES ('Contains ApplicationID 15, 16 and 100' ,N'<Submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Id="15" TestMode="false" SubmissionId="15" Status="0" ProcessId="0" Version="1" CreatedByUser="2" DateCreated="2016-09-28T15:08:25.667" DateDeleted="0001-01-01T00:00:00"> <Applications> <Application ApplicationID="15" ApplicationName="Test Form"> <FriendlyUrl>blah 15</FriendlyUrl> </Application> <Application ApplicationID="16" ApplicationName="Test Form"> <FriendlyUrl>blah 16</FriendlyUrl> </Application> <Application ApplicationID="100" ApplicationName="Test Form"> <FriendlyUrl>blah 100</FriendlyUrl> </Application> </Applications> </Submission>') ,('Contains ApplicationID 15, 17 and 200' ,N'<Submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Id="15" TestMode="false" SubmissionId="15" Status="0" ProcessId="0" Version="1" CreatedByUser="2" DateCreated="2016-09-28T15:08:25.667" DateDeleted="0001-01-01T00:00:00"> <Applications> <Application ApplicationID="15" ApplicationName="Test Form"> <FriendlyUrl>blah 15</FriendlyUrl> </Application> <Application ApplicationID="17" ApplicationName="Test Form"> <FriendlyUrl>blah 17</FriendlyUrl> </Application> <Application ApplicationID="200" ApplicationName="Test Form"> <FriendlyUrl>blah 200</FriendlyUrl> </Application> </Applications> </Submission>'); 

– Das ist, was du suchst

 --Try different values (15 returns both rows, 16, 100 or 200 just one row and any other value none DECLARE @ApplicationID INT=15; 

– Und das ist die Abfrage

Btw: Ich verwende Wildcard-Namensraum, um Verwirrung mit deinen tatsächlichen Namespaces zu vermeiden …

 SELECT Id ,TestText --This is just to show, how you would fetch a value from the given ApplicationID ,Applications.value('(/*:Submission/*:Applications/*:Application[(@*:ApplicationID)[1] eq sql:variable("@ApplicationID")]/*:FriendlyUrl)[1]','nvarchar(max)') FROM @Applications_Submissions WHERE Applications.exist('/*:Submission/*:Applications/*:Application[(@*:ApplicationID)[1] eq sql:variable("@ApplicationID")]')=1