CODEWITHSUNDEEP
sqlsql-serversqlxml
blu
blu

Reputation: 13175

SQL Server For Xml Path

I am returning an xml result for a query.

Given two tables:

**Foo**
FooId int
FooName varchar(10)

**Bar**
BarId int
FooId int (FK)
BarName varchar(10)

I create a select statement like:

SELECT
  FooId,
  FooName,
  (
    SELECT
      BarId,
      FooId,
      BarName
    FROM
      Bar
    WHERE
      Bar.FooId = Foo.FooId
      AND Bar.BarName = 'SomeBar'
    FOR XML PATH('Bar'), TYPE
  )
FROM
  Foo
WHERE
  Foo.FooName = 'SomeFoo'
  AND Foo.FooId IN
  (
    SELECT
      Bar.FooId
    FROM
      Bar
    WHERE
      Bar.BarName = 'SomeBar'
  )
FOR XML PATH('Foo'), TYPE

This works as I expect it and returns the correct results. I realized while developing it I needed to duplicate the filtering clauses in both the sub select and the nested selects in the where clause in order to get the correct results.

I'd like to make sure there isn't a better way to do this. A coworker mentioned using aliases to remove the duplicate clauses but am not sure how that would accomplish anything.

Is this necessary, or is there a better way?

Upvotes: 0

Views: 1595

Answers (1)

cdonner
cdonner

Reputation: 37668

Assuming that you only want to return Foos that have a Bar, and that you need the preserve the Bar node in the XML (i.e. a join will not work, since it flattens your XML), there is no way to simplify this by much. You can certainly put the Bar query into a common table expression, but the whole thing actually becomes longer if you do that.

Upvotes: 2

Related Questions