{"id":102257,"date":"2024-06-03T17:56:51","date_gmt":"2024-06-03T17:56:51","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=102257"},"modified":"2026-02-25T13:45:35","modified_gmt":"2026-02-25T13:45:35","slug":"effective-strategies-for-storing-and-parsing-xml-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/effective-strategies-for-storing-and-parsing-xml-in-sql-server\/","title":{"rendered":"Store, Parse & Convert XML in SQL Server"},"content":{"rendered":"

SQL Server provides a native XML data type with built-in support for XQuery, OPENXML, XML indexes, and schema validation. Store XML in XML-typed columns (not VARCHAR) to get type checking, XML-specific indexing, and native XQuery support. Parse XML values using the .value(), .query(), .nodes(), and .modify() methods, or use OPENXML for rowset-based shredding.<\/p>\n

Convert between XML and string types with CAST(xml_column AS NVARCHAR(MAX)) or CAST(string_column AS XML). For performance, create primary and secondary XML indexes on columns that are frequently queried. This guide covers the full lifecycle of XML in SQL Server – from storage and parsing to indexing, validation, and conversion.<\/p>\n

Introduction<\/h2>\n

XML is a common storage format for data, metadata, parameters, or other semi-structured data. Because of this, it often finds its way into SQL Server databases and needs to be managed alongside other data types.<\/p>\n

Even though a relational database is not the optimal place to store and manage XML data, it is often needed due to application requirements, convenience, or a need to maintain this information in close proximity to other app data.<\/p>\n

This article dives into a variety of common XML challenges and the functionality included in SQL Server to help make managing them as simple as possible.<\/p>\n

Store XML as XML. Store non-XML as non-XML.<\/h2>\n

XML will enter our data lives in one of two ways:<\/p>\n

    \n
  1. Data stored in an XML-typed column.<\/li>\n
  2. Data stored in some other format.<\/li>\n<\/ol>\n

    Before diving into managing XML, it is important to note that not all XML requires a data engineer to know advanced (or any) knowledge about how to index, filter, update, or use XML.<\/p>\n

    Sometimes, XML is stored in a database for convenience and is simply written or read by the application directly. In these scenarios, there is little architectural work involved aside from the need to have an XML-typed column available to store the data.<\/p>\n

    In many ways, this is an ideal situation. The purpose of a database is to store and retrieve data, and not needing to index, filter, or shred XML allows a database to do what it is best at while not introducing complexity into database or application code to manage it. If XML is stored in SQL Server, it will often need to be directly queried, even if filtering, aggregating, or other more advanced functionality is not needed.<\/p>\n

    If possible, store XML data in an XML typed column. Developers sometimes choose to store XML in string columns instead, such as VARCHAR(MAX)<\/code>. While strings are more familiar to us and easier to work with, they do not provide the many XML-related benefits that an XML-typed column does. Oftentimes, when XML functionality is later needed against a string column, the result is the awkward need to convert from VARCHAR<\/code> to XML and then back again, as needed. This is inefficient and creates additional opportunities for mistakes to occur as data is being frequently converted, in addition to written, read, filtered, etc…<\/p>\n

    Similarly: do not store data as XML unless needed. Data or metadata that will be routinely consumed by SQL Server ideally should be stored in a format that is quick and easy to read\/write. Whether in a configuration table, metadata table, temporal table, or some other convenient data structure, consider the ideal candidate for storing data before finalizing the decision.<\/p>\n

    Data types are database design decisions that have long lives ahead of them. Choosing the wrong data type for an important data element can result in poor performance, wasted time and effort, and technical debt that remains for years to come. Therefore, be sure to store XML in XML-typed columns and non-XML in non-XML data types.<\/p>\n

    Parsing XML<\/h2>\n

    The most common need with XML is to read it, either in its entirety or by pulling out specific details from a document. For the demos in this article, the dbo.DatabaseLog<\/code> table will be used, which can be found in the AdventureWorksDW<\/code> database. (You can download a backup to restore here<\/a> from the Microsoft site. I am using the 2022 version of the database.)<\/p>\n

    This table contains logs of SQL commands used to create or modify database objects. For example, the following query retrieves a single row from the table:<\/p>\n

    SELECT\n     *\nFROM dbo.DatabaseLog\nWHERE DatabaseLog.PostTime = '2017-10-27 14:36:32.110';<\/pre>\n
    The results show a column called XmlEvent<\/code>:<\/p>\n
    <\/p>\n
    The simplest way to read data from an XML column is by using an XQuery against it. In the following example, several elements are returned, each as a separate column:<\/p>\n
    SELECT\n  DatabaseLog.XMLEvent.value('(EVENT_INSTANCE\/EventType)[1]',\n                              'VARCHAR(MAX)') AS EventType,\n  DatabaseLog.XMLEvent.value('(EVENT_INSTANCE\/ServerName)[1]', \n                              'VARCHAR(MAX)') AS ServerName,\n  DatabaseLog.XMLEvent.value('(EVENT_INSTANCE\/LoginName)[1]', \n                               'VARCHAR(MAX)') AS LoginName,  \n  DatabaseLog.XMLEvent.value\n          ('(EVENT_INSTANCE\/TSQLCommand\/CommandText)[1]'\n                             , 'VARCHAR(MAX)') AS SetOptions\nFROM dbo.DatabaseLog\nWHERE DatabaseLog.PostTime = '2017-10-27 14:36:32.110';<\/pre>\n
    Each column specifically retrieves the first element for the various tags provided. The results look like this:<\/p>\n
    <\/p>\n
    The data type provided alongside the tag detail is the data type that the result will be returned as. Using VARCHAR(MAX)<\/code> is overkill for EventType<\/code>, ServerName<\/code>, and LoginName<\/code>, as these columns will not exceed 128 characters. It is a fairly typical process to start with a large datatype as you are interrogating the data in XML. Moving forward in this article, data types will be chosen that are more fitting for the data elements provided.<\/p>\n
    This syntax is perfect when you know exactly what data elements are needed, what they are called, and where to find them. This is an easy way to parse XML when it is served to you nicely on a platter. If only life were always so easy!<\/p>\n
    OPENXML<\/code> may also be used to parse an XML document. This is ideally used on a scalar variable or XML that is read into a single variable\/parameter. An extra step is needed to prepare the XML document, which converts it into a format that SQL Server can easily consume:<\/p>\n
    DECLARE @XMLDoc INT;\nDECLARE @XMLEvent XML = '\n<EVENT_INSTANCE>\n  <EventType>ALTER_TABLE<\/EventType>\n  <PostTime>2017-10-27T14:36:32.113<\/PostTime>\n  <SPID>62<\/SPID>\n  <ServerName>BARBKESS24\\MSSQL2017RTM<\/ServerName>\n  <LoginName>REDMOND\\barbkess<\/LoginName>\n  <UserName>dbo<\/UserName>\n  <DatabaseName>AdventureWorksDW2017<\/DatabaseName>\n  <SchemaName>dbo<\/SchemaName>\n  <ObjectName>DimPromotion<\/ObjectName>\n  <ObjectType>TABLE<\/ObjectType>\n  <AlterTableActionList>\n    <Create>\n      <Constraints>\n        <Name>PK_DimPromotion_PromotionKey<\/Name>\n      <\/Constraints>\n    <\/Create>\n  <\/AlterTableActionList>\n  <TSQLCommand>\n    <SetOptions ANSI_NULLS=\"ON\" ANSI_NULL_DEFAULT=\"ON\" ANSI_PADDING=\"ON\" QUOTED_IDENTIFIER=\"ON\" ENCRYPTED=\"FALSE\" \/>\n    <CommandText>ALTER TABLE [dbo].[DimPromotion] WITH CHECK ADD \n    CONSTRAINT [PK_DimPromotion_PromotionKey] PRIMARY KEY CLUSTERED \n    (\n       [PromotionKey]\n    )  ON [PRIMARY]<\/CommandText>\n  <\/TSQLCommand>\n<\/EVENT_INSTANCE>\n';\nEXEC sys.sp_xml_preparedocument @XMLDoc OUTPUT, @XMLEvent;<\/pre>\n
    The script above takes the same XML from the previous example, stores it in a scalar XML variable, and then converts it using sp_xml_preparedocument<\/code>, placing the result into @XMLDoc<\/code>. (Note that as this is a variable, you will need to execute the following SELECT<\/code> statement in the same batch of code for it to execute.)<\/p>\n
    From here, there are a handful of options available. The first allows for specific elements to be retrieved from the XML document, like this:<\/p>\n
    SELECT\n     *\nFROM OPENXML (@XMLDoc, '\/EVENT_INSTANCE', 2)\nWITH (EventType VARCHAR(50),\n      ServerName VARCHAR(128),\n      LoginName VARCHAR(128),\n      TSQLCommand VARCHAR(MAX));<\/pre>\n
    This syntax will explicitly return the same four elements that were previously parsed out of the XML document. The second parameter (2) indicates that the XML is element centric. If an XML document is attribute-centric, then set this to 1.<\/p>\n
    Ensure that data types are correct, or an error will be returned. For example, if EventType<\/code> were declared as an INT, then the following error would be thrown.<\/p>\n
    Msg 245, Level 16, State 1, Line 61
    \n<\/code>Conversion failed when converting the nvarchar value 'ALTER_TABLE' to data type int.<\/code><\/p>\n
    If there is a need to return everything from an XML document, then consider the following syntax, instead:<\/p>\n
    SELECT\n     *\nFROM OPENXML (@XMLDoc, '\/EVENT_INSTANCE'); \n-- No value for XML flag: All elements returned \n--in an edge table.<\/pre>\n
    The result will be a hierarchical table that looks like this:<\/p>\n
    <\/p>\n
    Note that while the column headers will the same no matter what sort of XML document is parsed, the content of the rows will vary greatly. There are many ways to use this data, such as:<\/p>\n