Returning XML From Multiple Elements in MS SQL

Question

I have the following XML stored in an MS SQL table.

<CustomerStrategy>
  <ChampionChallenger  GroupId="BureauPhoneTest" IsChampion="1" StrategyName="New Bureau Phone Champion" IsSelected="true" />
  <ChampionChallenger GroupId="BureauPhoneTest" IsChampion="0"  StrategyName="New Bureau Phone Challenger" IsSelected=""/>
</CustomerStrategy>

I'm looking for those that are IsChampion = "0" and IsSelected="true" and GroupID="BureauPhoneTest". There could be other champion challenger elements in the CustomerStrategy element.

How do I structure my SQL to only return IsChampion = "0" and IsSelected="true" and GroupID="BureauPhoneTest".

gofr1 · Accepted Answer · 2016-12-16 14:15:46Z

1

You can directly work with XML like:

DECLARE @x TABLE (
    id int,
    xmlField xml
)

INSERT INTO @x VALUES
(1, '<CustomerStrategy>
  <ChampionChallenger  GroupId="BureauPhoneTest" IsChampion="1" StrategyName="New Bureau Phone Champion" IsSelected="true" />
  <ChampionChallenger GroupId="BureauPhoneTest" IsChampion="0"  StrategyName="New Bureau Phone Challenger" IsSelected=""/>
</CustomerStrategy>'),
(2, '<CustomerStrategy>
  <ChampionChallenger  GroupId="BureauPhoneTest" IsChampion="1" StrategyName="New Bureau Phone Champion" IsSelected="true" />
  <ChampionChallenger GroupId="BureauPhoneTest" IsChampion="0"  StrategyName="New Bureau Phone Challenger" IsSelected="true"/>
</CustomerStrategy>')


SELECT DISTINCT x.id
FROM @x x
CROSS APPLY (
    SELECT xmlField
    FROM xmlField.nodes('/CustomerStrategy/ChampionChallenger') as t(c)
    WHERE t.c.value('@IsChampion','bit') = 0 
            and t.c.value('@IsSelected','nvarchar(6)') = 'true'
            and t.c.value('@GroupId','nvarchar(50)') ='BureauPhoneTest'         
    ) as t

This will return 2. I add one string with all conditions required.

answered Dec 16, 2016 at 14:15

gofr1

16k11 gold badges47 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3179585 Over a year ago

Is, essentially, we are taking the XML and turning it into a type of temp table. Am I correct?

user3179585 Over a year ago

I meant "So", not "Is".

Gottfried Lesigang · Accepted Answer · 2016-12-16 14:36:13Z

It is better to inlcude the filter within .nodes() as XQuery expression.

Especially with big XML it is an overkill first to read everything, just to filter it out at the end.

The following will return NULL for id=1, one XML for id=2 and two entries for id=3:

DECLARE @tbl TABLE (id int,xmlField xml);
INSERT INTO @tbl VALUES
(1, '<CustomerStrategy>
  <ChampionChallenger  GroupId="BureauPhoneTest" IsChampion="1" StrategyName="New Bureau Phone Champion" IsSelected="true" />
  <ChampionChallenger GroupId="BureauPhoneTest" IsChampion="0"  StrategyName="New Bureau Phone Challenger" IsSelected=""/>
</CustomerStrategy>'),
(2, '<CustomerStrategy>
  <ChampionChallenger  GroupId="BureauPhoneTest" IsChampion="1" StrategyName="New Bureau Phone Champion" IsSelected="true" />
  <ChampionChallenger GroupId="BureauPhoneTest" IsChampion="0"  StrategyName="New Bureau Phone Challenger" IsSelected="true"/>
</CustomerStrategy>'),
(3, '<CustomerStrategy>
  <ChampionChallenger  GroupId="BureauPhoneTest" IsChampion="0" StrategyName="New Bureau Phone Champion" IsSelected="true" />
  <ChampionChallenger GroupId="BureauPhoneTest" IsChampion="0"  StrategyName="New Bureau Phone Challenger" IsSelected="true"/>
</CustomerStrategy>');

SELECT t.id
      ,Filtered.query('.') AS FilteredNodes
FROM @tbl AS t
OUTER APPLY xmlField.nodes(N'/CustomerStrategy/ChampionChallenger[@IsChampion eq "0" and @IsSelected eq "true" and @GroupId eq "BureauPhoneTest"]') AS A(Filtered)

Collectives™ on Stack Overflow

Returning XML From Multiple Elements in MS SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related