\nIf I store XML in an XML data type column its sometimes useful for performance to be able to hoist the value of a particular item to its own persisted computed column. The column can then be used in SQL queries without having to recalculate the XQuery each time its accessed. For example, if I want to use an attribute at the root named orderid:\n<\/p>\n
\nCREATE TABLE dbo.xml_order (
\n id INT PRIMARY KEY IDENTITY, — to enable XML Index creation
\n order_doc XML,
\n orderid — this should be obtained from XML document
\n)\n<\/p>\n
\nI must first define a SQL Server UDF to calculate the value.\n<\/p>\n
\nCREATE FUNCTION dbo.getorderid (@data XML)
\nRETURNS INT
\nWITH SCHEMABINDING
\nAS
\nBEGIN
\n RETURN @data.value('\/*[1]\/@orderid', 'INT')
\nEND\n<\/p>\n
\nand then use it:\n<\/p>\n
\nCREATE TABLE xml_order (
\n id INT PRIMARY KEY IDENTITY, — to enable XML Index creation
\n order_doc XML,
\n orderid AS dbo.getorderid(order_doc) PERSISTED
\n)\n<\/p>\n
\nThe "WITH SCHEMABINDING" is needed if I want to the column a persisted computed column. The persisted computed column requires the function to be deterministic (returns same output give same input).\n<\/p>\n
\nThis got me thinking…which types of XQuery statements are deterministic and what determines determinism in an XQuery statement? Turns out that ALL XQuery statements are deterministic, because the current SQL Server XQuery dialect doesn't contain non-deterministic XQuery functions like current-dateTime() and friends. Note that this could change as SQL Server XQuery adds (I hope) more of the standard XQuery functions and operators in future. But for now, all is safe and determnistic.<\/p>\n","protected":false},"excerpt":{"rendered":"
If I store XML in an XML data type column its sometimes useful for performance to be able to hoist the value of a particular item to its own persisted computed column. The column can then be used in SQL queries without having to recalculate the XQuery each time its accessed. For example, if I […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37],"tags":[],"class_list":["post-875","post","type-post","status-publish","format-standard","hentry","category-sql-server-xml"],"yoast_head":"\n