You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Of the methods on an xml column, only .query() fails:
Expression
Result
doc.value('(/order/@id)[1]', 'INT')
✅
doc.exist('/order')
✅
o.doc.nodes('/order/item') AS i(n) in CROSS APPLY
✅
doc.query('/order/item')
❌ unexpected token: query
SELECTdoc.query('/order/item') FROM orders;
Expected: a function call on the column (FunctionCall with a CallTarget in SqlScriptDOM), like .value(). QUERY is probably lexed as a keyword after the .. That position is a method name, so any identifier, keywords included, should be accepted there.
CREATETABLEorders_xml (id INT IDENTITY PRIMARY KEY, doc XML NOT NULL);
SELECT id, doc.value('(/order/@id)[1]', 'INT') AS order_id, doc.query('/order/item') AS items
FROM orders_xml WHEREdoc.exist('/order[@status=sql:variable("@status")]') =1;
SELECTo.id, i.n.value('@sku', 'NVARCHAR(20)') AS sku
FROM orders_xml o CROSS APPLY o.doc.nodes('/order/item') AS i(n);
Activity