CODEWITHSUNDEEP
sqlxmlxquerysequence
Torsten
Torsten

Reputation: 1

xml.exist - sql:column to sequence

I try to create a query with xml.exist by using the sql:column-function where the value of the column should be transformed into a usable XQuery-sequence.

This is the query with a static sequence, which is working.

SELECT
    FieldA
    , FieldB
FROM
    MyTable
WHERE
    FieldC.exist('DSAuth/role[@id=("195", "267", "350")')

To get a dynamic sequence i would use a table function with returns a table with a column "IDsequence" that have all id's as a string. e.g. ' "195", "267", "350" '.

The table function should return only one row! With multiple rows it works, but i have to group the result at last, which is bad for performance.

SELECT
    FieldA
    , FieldB
FROM
    MyTable
CROSS APPLY
    dbo.MyFunc(0) AS f
WHERE
    FieldC.exist('DSAuth/role[@id=sql:column("f.IDsequence")]')

Is there a way to get a usable sequence for XQuery from sql:column("f.IDsequence")?

Thanks for help.

Edit:

The problem in performance is that FieldB (and a few more fields) is a xml column, so i have to convert it to group by.

SELECT
    FieldA
    , CAST(CAST(FieldB AS nvarchar(max)) AS xml) AS FieldB
FROM
    MyTable
CROSS APPLY
    dbo.MyFunc(0) AS f
WHERE
    FieldC.exist('DSAuth/role[@id=sql:column("f.IDsequence")]')
GROUP BY
    FieldA
    , CAST(FieldB AS nvarchar(max))

Upvotes: 0

Views: 95

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

I don't know if this is the easiest approach, but you might try to include your list into the XML and use it in the predicate like here:

--A mockup-table to show the principles

DECLARE @tbl TABLE(TheXml XML)
INSERT INTO @tbl VALUES
(
    N'<root>
        <a>1</a>
        <a>2</a>
      </root>'
)
,(
    N'<root>
        <a>1</a>
      </root>'
)
,(
    N'<root>
        <a>3</a>
        <a>4</a>
      </root>'
);

--This is easy: Just one value

SELECT * FROM @tbl WHERE TheXml.exist('/root/a[. cast as xs:int?=1]')=1;

--This is your sequence.
--But you cannot introduce the value list with sql:column() or sql:variable().

SELECT * FROM @tbl WHERE TheXml.exist('/root/a[. cast as xs:int?=(2,3)]')=1;

--But you can add the values to your XML before like in this cte

DECLARE @values TABLE(val INT);
INSERT INTO @values VALUES(2),(3);

WITH cte(NewXml) AS
(
    SELECT (SELECT (SELECT val AS [@val] FROM @values FOR XML PATH('values'),TYPE)
                  ,TheXml AS [*] 
            FOR XML PATH(''),TYPE
           )
    FROM @tbl t 
)
SELECT NewXml.query('/root') TheXml
FROM cte
WHERE NewXml.exist('/root/a[. cast as xs:int?=/values/@val]')=1;

The intermediate XML looks like this:

<values val="2" />
<values val="3" />
<root>
  <a>1</a>
  <a>2</a>
</root>

The final .query('/root') will return the previous XML unchanged.

UPDATE

The same would work with an introduction on string base like here:

WITH cte(NewXml) AS
(
    SELECT (SELECT CAST(N'<values val="2" /><values val="3" />' AS XML)
                  ,TheXml AS [*] 
            FOR XML PATH(''),TYPE
           )
    FROM @tbl t 
)
SELECT NewXml.query('/root') TheXml
FROM cte
WHERE NewXml.exist('/root/a[. cast as xs:int?=/values/@val]')=1

Upvotes: 0

Related Questions