Get SQL records based on XML attribute values

Question

I have done some basic XML querying using T-SQL but I am not sure how to attack this problem.

I would like to query all rows in a table where a specific XML attribute has a requested value.

Here is my table schema:

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]

The XML data stored in the [Application] column is structured like this:

<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>

I would like to get the table rows based on the ApplicationID attribute that is stored in the <Application> node. Each <Submission> can have multiple <Application> nodes.

What is the best way of doing this?

Thank you so much!

Gottfried Lesigang · Accepted Answer · 2016-10-25 08:58:01Z

The best for this was xml.exist(). But this will never be fast... If you need this more often or with bigger counts of rows you should rather extract the ApplicationIDs together with the actual rowID into a side table. This can be done in a trigger.

But you might try this:

--Mock-up table

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

--Insert two mock-up rows

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>');

--This is, what you are searching for

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

--And this is the query

--Btw: I use wildcard-namespace to avoid confusion with your actual namespaces...

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

Thank you! This worked like a charm. I ended up creating the table to store the applications as they came in via an INSERT/UPDATE trigger.

Collectives™ on Stack Overflow

Get SQL records based on XML attribute values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related