{"id":1428,"date":"2012-10-23T00:00:00","date_gmt":"2012-10-23T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/manipulating-xml-data-in-sql-server\/"},"modified":"2026-04-16T09:45:35","modified_gmt":"2026-04-16T09:45:35","slug":"manipulating-xml-data-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/manipulating-xml-data-in-sql-server\/","title":{"rendered":"SQL Server XML: Creating, Shredding, and Combining XML Data with T-SQL"},"content":{"rendered":"
\n

SQL Server provides a rich set of T-SQL tools for working with XML data. Creating XML from relational tables uses FOR XML (with AUTO, PATH, or EXPLICIT modes). Shredding XML – extracting data from an XML structure into relational rows – uses OPENXML with sp_xml_preparedocument, or the XQuery value(), nodes(), and query() methods. Namespaces require WITH XMLNAMESPACES declarations. The XQuery nodes() method enables efficient row-by-row shredding of repeating XML elements. This article covers all of these with working T-SQL examples, including how to combine separate XML instances and separate combined XML back into individual nodes.<\/strong><\/p>\n

It often becomes necessary to create, shred, combine, or otherwise reconstitute XML data, in order to make it fit a specific purpose. Sometimes business requirements dictate that XML fragments should be merged, while other requests call for XML documents or fields to be shredded and their values imported into tables. Sometimes, XML Data must be created directly from existing tables. SQL Server provides plenty of XML-related tools, but how can we know which ones to use, and when?<\/p>\n

Let’s examine some of these tasks that require XML manipulation, using the sample AdventureWorks2012<\/strong><\/a> database (Other AdventureWorks versions should work OK, but there may be variations in the data and\/or table schemas).<\/p>\n

Creating XML<\/h2>\n

One common requirement is to create an XML structure that is based on the schema of an existing table. Let’s assume that we’ve received a request to create XML data from relevant fields in the Person.Person<\/strong> table, for the person having BusinessEntityID 10<\/strong>0<\/strong>01<\/strong>. We need to gather the values from this row:<\/p>\n

-- select Person record\nSELECT *\nFROM Person.Person\nWHERE BusinessEntityID = 10001\nGO<\/pre>\n
\"1585-1-dd221bb4-51fd-4759-847e-dc0a3b960\"<\/p>\n
We need to include the BusinessEntityID field and some of the name-data columns in the new XML structure. Note that there happens to be an existing XML column in the table – the Demographics<\/strong> field.<\/p>\n
SQL Server provides an XML option to use with the FOR clause, allowing for an easy method of converting table data into XML nodes<\/em>. FOR XML<\/a> can take different arguments – let’s find out which one works for us.<\/p>\n
The AUTO<\/a> argument is one of the simplest to use. It creates one node for each record returned by the SELECT clause:<\/p>\n
-- create XML structure using FOR XML AUTO\nSELECT BusinessEntityID, \n\u00a0\u00a0 PersonType, \n\u00a0\u00a0 Title, \n\u00a0\u00a0 FirstName, \n\u00a0\u00a0 MiddleName, \n\u00a0\u00a0 LastName, \n\u00a0\u00a0 Suffix\nFROM Person.Person\nWHERE BusinessEntityID = 10001\nFOR XML AUTO\nGO<\/pre>\n
\"1585-1-bf14dd7c-2785-4fac-927e-169aec625\"<\/p>\n
By default, the AUTO argument organizes every non-XML field into a node attribute<\/strong>. To specify that the values be created as node elements<\/strong>, not attributes, we can additionally specify the ELEMENTS argument:<\/p>\n
-- create XML structure using FOR XML AUTO\nSELECT BusinessEntityID, \n\u00a0\u00a0 PersonType, \n\u00a0\u00a0 Title, \n\u00a0\u00a0 FirstName, \n\u00a0\u00a0 MiddleName, \n\u00a0\u00a0 LastName, \n\u00a0\u00a0 Suffix\nFROM Person.Person\nWHERE BusinessEntityID = 10001\nFOR XML AUTO, ELEMENTS\nGO<\/pre>\n
The resulting XML:<\/p>\n
\"1585-1-af99ca70-419c-41fc-8b44-04d37130a\"<\/p>\n
The ELEMENTS argument causes every value to be created as a <\/em>node element<\/em><\/strong>. Now we have a separate node for every value, and a wrapper root node. In the first example the resulting XML had the same data, but the values were rendered as attributes.<\/p>\n
We notice that the root node name is the schema and table name (Person.Person<\/strong>). We would like to change this to ‘Person<\/strong>‘. To designate a custom root element, we’ll use the PATH<\/a> argument instead of AUTO:<\/p>\n
-- create XML using FOR XML PATH\nSELECT\u00a0\u00a0BusinessEntityID, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PersonType, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Title, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FirstName, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 MiddleName, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LastName, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Suffix\nFROM Person.Person\nWHERE BusinessEntityID = 10001\nFOR XML PATH('Person')\nGO<\/pre>\n
The results:<\/p>\n
\"1585-1-90d45dec-f3f9-451e-8986-66204f168\"<\/p>\n
Using the PATH argument has enabled us to specify the name ‘Person’ in our root wrapper.<\/p>\n
Combining Node Attributes and Elements<\/h2>\n
What if we want one or a few values to be created as node attributes, thereby resulting in a combination of node attributes and elements<\/em>? We can create node attribute values by simply designating column aliases that use the ‘@’ symbol:<\/p>\n
-- designate node attribute\nSELECT BusinessEntityID AS '@ID', \n\u00a0\u00a0 PersonType, \n\u00a0\u00a0 Title, \n\u00a0\u00a0 FirstName, \n\u00a0\u00a0 MiddleName, \n\u00a0\u00a0 LastName, \n\u00a0\u00a0 Suffix\nFROM Person.Person\nWHERE BusinessEntityID = 10001\nFOR XML PATH('Person')\nGO<\/pre>\n
The resulting XML:<\/p>\n
\"1585-1-05fc0f0c-049c-40c1-b96c-2dd9de947\"<\/p>\n
Including XML Columns<\/h2>\n
What happens if we add the XML field (Demographics<\/strong>) to our SELECT clause?<\/p>\n
-- include existing XML column\nSELECT BusinessEntityID AS '@ID', \n\u00a0\u00a0 PersonType, \n\u00a0\u00a0 Title, \n\u00a0\u00a0 FirstName, \n\u00a0\u00a0 MiddleName, \n\u00a0\u00a0 LastName, \n\u00a0\u00a0 Suffix,\n\u00a0\u00a0 Demographics\nFROM Person.Person\nWHERE BusinessEntityID = 10001\nFOR XML PATH('Person')\nGO<\/pre>\n
\"1585-1-2460a973-7412-4eed-9ade-570e92b7c\"<\/p>\n
We see that an existing XML field is created as a nested node element<\/em>. Note that the XML namespace data is included in the nested node.<\/p>\n
Shredding XML<\/h2>\n
‘Shredding’ XML data is another common request. To ‘shred’ means to strip the actual data away from the markup tags, and organize it into a relational format. For example, shredding is what happens when an XML document is imported into a table, when each node value is mapped to a specific field in the table. A popular method to use for this is to use the OPENXML()<\/a> function, but XQuery methods<\/a> can also be engaged to perform the same tasks. OPENXML() was available to use for shredding before the SQL Server XQuery methods were introduced, and is somewhat faster for larger data operations. However, it is decidedly more complex to use, and is more memory intensive. Also, OPENXML() cannot take advantage of XML indexes<\/a> as XQuery methods can.<\/p>\n
We’ve received another request: pull data from some of the nodes (Occupation, Education, <\/strong>HomeOwnerFlag<\/strong>, <\/strong>NumberCarsOwned<\/strong>) that are contained in the Person.Person table’s Demographics<\/strong> XML column for BusinessEntityID 15291<\/strong>, and display it along with other non-XML field values (FirstName<\/strong>, <\/strong>MiddleName<\/strong>, <\/strong>LastName<\/strong>) from the table.<\/p>\n
The Person.Person record for BusinessEntityID 15291<\/strong>, and their expanded Demographics XML instance are shown below:<\/p>\n
SELECT *\nFROM Person.Person\nWHERE BusinessEntityID = 15291\nGO<\/pre>\n
\"1585-1-4cc25090-8bb1-4318-a1ae-8eaf7c89a\"<\/p>\n
\"1585-1-04042007-3f5c-4266-bb35-2aa6983d5\"<\/p>\n
The XQuery value()<\/a> method is an easy way to extract values from XML data while preserving the data types:<\/p>\n
-- extract (shred) values from XML column nodes\nSELECT FirstName, \n\u00a0\u00a0 MiddleName,\n\u00a0\u00a0 LastName,\n\u00a0\u00a0 Demographics.value('declare namespace ns=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/IndividualSurvey\"; (\/ns:IndividualSurvey\/ns:Occupation)[1]','varchar(50)') AS Occupation,\n\u00a0\u00a0 Demographics.value('declare namespace ns=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/IndividualSurvey\"; (\/ns:IndividualSurvey\/ns:Education)[1]','varchar(50)') AS Education,\n\u00a0\u00a0 Demographics.value('declare namespace ns=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/IndividualSurvey\"; (\/ns:IndividualSurvey\/ns:HomeOwnerFlag)[1]','bit') AS HomeOwnerFlag,\n\u00a0\u00a0 Demographics.value('declare namespace ns=\"http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/IndividualSurvey\"; (\/ns:IndividualSurvey\/ns:NumberCarsOwned)[1]','int') AS NumberCarsOwned\nFROM Person.Person\nWHERE BusinessEntityID = 15291\nGO<\/pre>\n
\"1585-1-7d946213-f2ea-4357-aeae-f6d4dd49d\"<\/p>\n
XML Namespaces<\/h2>\n
While this returns the shredded result<\/strong> that we want, the repetitive namespace declarations expand the size of our query – since we are returning four XML node values, we have to declare the namespace four times. Declaring the namespace is necessary because the Demographics<\/strong> XML structure uses typed XML<\/a> – its XML data is associated with an XML schema. However, we can use a WITH XML NAMESPACES<\/a> clause to declare the XML namespace instead – this lets us to declare the namespace only once for the entire code block:<\/p>\n
-- extract (shred) values from XML column nodes using WITH XMLNAMESPACES\n;WITH XMLNAMESPACES ('http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/IndividualSurvey' AS ns)\nSELECT FirstName, \n\u00a0\u00a0 MiddleName,\n\u00a0\u00a0 LastName,\n\u00a0\u00a0 Demographics.value('(\/ns:IndividualSurvey\/ns:Occupation)[1]','varchar(50)') AS Occupation,\n\u00a0\u00a0 Demographics.value('(\/ns:IndividualSurvey\/ns:Education)[1]','varchar(50)') AS Education,\n\u00a0\u00a0 Demographics.value('(\/ns:IndividualSurvey\/ns:HomeOwnerFlag)[1]','bit') AS HomeOwnerFlag,\n\u00a0\u00a0 Demographics.value('(\/ns:IndividualSurvey\/ns:NumberCarsOwned)[1]','int') AS NumberCarsOwned\nFROM Person.Person\nWHERE BusinessEntityID = 15291\nGO<\/pre>\n
XQuery nodes() Method<\/h2>\n
The XQuery nodes()<\/a> method is another option that allows us to specify a particular node set<\/strong> in which to look for the desired child nodes:<\/p>\n
-- extract (shred) values from XML column nodes using XQuery nodes() method\n;WITH XMLNAMESPACES ('http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/IndividualSurvey' AS ns)\nSELECT FirstName, \n\u00a0\u00a0 MiddleName,\n\u00a0\u00a0 LastName,\n\u00a0\u00a0 C.value('ns:Occupation[1]','varchar(50)') AS Occupation,\n\u00a0\u00a0 C.value('ns:Education[1]','varchar(50)') AS Education,\n\u00a0\u00a0 C.value('ns:HomeOwnerFlag[1]','bit') AS HomeOwnerFlag,\n\u00a0\u00a0 C.value('ns:NumberCarsOwned[1]','int') AS NumberCarsOwned\nFROM Person.Person\nCROSS APPLY Demographics.nodes('\/ns:IndividualSurvey') AS T(C)\nWHERE BusinessEntityID = 15291\nGO<\/pre>\n
We’ve used the nodes() method to drill down (one level) to the location of the ‘IndividualSurvey<\/strong>‘ node, and then returned the actual values via the XQuery value() method. We used CROSS APPLY to join the node set back to the table. A CROSS APPLY would not have been necessary if we were shredding an XML variable, instead of from an XML column in a table. The nodes() method easily shreds XML data from XML columns as well as from XML variables. The nodes() method requires table and column aliases (T<\/strong>(C<\/strong>)<\/strong>) in order for other XQuery methods (like the value() method) to access the node set that the nodes() method returns. The column and table alias names are irrelevant.<\/p>\n
Notice that we’ve used a WITH XML NAMESPACES<\/a> clause to declare the XML namespace. Declaring the namespace is necessary because the Demographics XML structure uses typed XML<\/a> – its XML data is associated with an XML schema.<\/p>\n
Now that our XML data has been shredded, the results can be stored in a table or combined with other queries.<\/p>\n
Nodes() Method Application and Efficiency<\/h2>\n
Keep in mind that using the nodes() method in simple queries can unnecessarily reduce query efficiency<\/strong>. When run in the same batch, our query that used the nodes() method cost 54% of the batch, where the value()-method-only query cost just 46%:<\/p>\n
-- using value() method only\n;WITH XMLNAMESPACES ('http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/IndividualSurvey' AS ns)\nSELECT FirstName, \n\u00a0\u00a0 MiddleName,\n\u00a0\u00a0 LastName,\n\u00a0\u00a0 Demographics.value('(\/ns:IndividualSurvey\/ns:Occupation)[1]','varchar(50)') AS Occupation,\n\u00a0\u00a0 Demographics.value('(\/ns:IndividualSurvey\/ns:Education)[1]','varchar(50)') AS Education,\n\u00a0\u00a0 Demographics.value('(\/ns:IndividualSurvey\/ns:HomeOwnerFlag)[1]','bit') AS HomeOwnerFlag,\n\u00a0\u00a0 Demographics.value('(\/ns:IndividualSurvey\/ns:NumberCarsOwned)[1]','int') AS NumberCarsOwned\nFROM Person.Person\nWHERE BusinessEntityID = 15291\nGO\n-- using nodes() method\n;WITH XMLNAMESPACES ('http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/IndividualSurvey' AS ns)\nSELECT FirstName, \n\u00a0\u00a0 MiddleName,\n\u00a0\u00a0 LastName,\n\u00a0\u00a0 C.value('ns:Occupation[1]','varchar(50)') AS Occupation,\n\u00a0\u00a0 C.value('ns:Education[1]','varchar(50)') AS Education,\n\u00a0\u00a0 C.value('ns:HomeOwnerFlag[1]','bit') AS HomeOwnerFlag,\n\u00a0\u00a0 C.value('ns:NumberCarsOwned[1]','int') AS NumberCarsOwned\nFROM Person.Person\nCROSS APPLY Demographics.nodes('\/ns:IndividualSurvey') AS T(C)\nWHERE BusinessEntityID = 15291\nGO<\/pre>\n
\"1585-1-4694e9cb-4e9c-4855-a343-4aaa2670b\"<\/p>\n
In light of this, why use the nodes() method at all? For simple queries, it’s probably better not to use it, although the batch cost or query time differences may be negligible. We’ve shown the use of nodes() in a very basic example, but it can also be used in more complex queries where it is necessary to return subsets of node sets<\/a> – using nodes() on a nodes() result. It’s also very convenient to use for constructing new XML from existing nodes. Since nodes() works by rendering logical portions of XML instances as node sets, it’s ideal for when query results must be returned in node form.<\/p>\n
Combining XML<\/h2>\n
Another, perhaps more uncommon procedure, would be to merge XML data from different instances. Let’s do just that to demonstrate – we’ll combine all of the store survey<\/strong> data from the Sales.Store<\/strong> table into one XML structure, for SalesPersonID 282<\/strong>. The store survey data is in the Demographics<\/strong> XML column. We also want to include 2 non-XML fields for each store: Name and BusinessEntityID, as node attributes in a parent ‘Store<\/strong>‘ node. To complete the process, we’ll wrap the final XML structure with a ‘StoreSurveys<\/strong>‘ root node.<\/p>\n
Let’s get a 5-record sample from the Sales.Store table, for SalesPersonID 282<\/strong>:<\/p>\n
--get top 5 records for SalesPerson 282\nSELECT TOP 5 *\nFROM Sales.Store\nWHERE SalesPersonID = 282\nGO<\/pre>\n
\"1585-1-f2b19492-7397-4ea4-9d15-2b3c2acbe\"<\/p>\n
The store survey XML data (Demographics<\/strong> column) for the Vinyl and Plastic Goods Corporation<\/strong> store looks like this:<\/p>\n
\"1585-1-a1758079-2489-4817-b02e-5918efb77\"<\/p>\n
To collect all of the store survey data for one sales person into one XML instance, we can again use the FOR XML clause. As with our previous use of FOR XML, the AUTO argument will be the first one we try. Remember that the AUTO argument, by default, gathers every non-XML field into an XML structure in node attribute form<\/em>. Like the PATH argument, it also nests any existing XML column data as element nodes<\/em>:<\/p>\n
-- combine store survey XML data\nSELECT Name, \nBusinessEntityID AS ID, \n\u00a0\u00a0 Demographics.query('\/')\nFROM Sales.Store AS Store\nWHERE SalesPersonID = 282\nFOR XML AUTO\nGO<\/pre>\n
\"1585-1-d09c2718-0053-4293-8773-0dbdae8bd\"<\/p>\n
We used the XQuery query()<\/a> method to query starting at the root of each XML instance (by using the ‘\/<\/strong>‘ in the path expression<\/a>). It looks like we have the result we want, except that there is no root node surrounding the entire structure. We used the PATH argument in our other FOR XML example, but we cannot use it here, since we are using AUTO to obtain the node attribute values. To build the wrapper node while using the AUTO argument, we’ll use the ROOT<\/a> argument:<\/p>\n
-- combine store survey XML data using ROOT argument\nSELECT Name, \n\u00a0\u00a0 BusinessEntityID AS ID, \n\u00a0\u00a0 Demographics.query('\/')\nFROM Sales.Store AS Store\nWHERE SalesPersonID = 282\nFOR XML AUTO, ROOT('StoreSurveys')\nGO<\/pre>\n
\"1585-1-d7e15cc7-fcd5-41e8-92e8-5199562d0\"<\/p>\n
The addition of the ROOT argument gives us the top-level wrapper node we wanted, merging all store survey records for the sales person into one XML instance with a root node.<\/p>\n
Separating XML<\/h2>\n
If we were to perform the converse<\/em> of the previous example, we would need to pull out the individual ‘StoreSurvey<\/strong>‘ node block from the combined store surveys XML instance (shown above), for each store name or ID. Separating out chunks of XML data into logical records essentially involves the same procedures we use when shredding XML, but in this case we will preserve of a portion of the XML structure. To demonstrate, let’s combine the store surveys for SalesPersonID 282<\/strong> again; this time, however, we will use an XML variable to store the combined store surveys:<\/p>\n
-- create XML instance of store survey data, using XML variable\nDECLARE @xml XML\n\nSET @xml = (\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0\u00a0Name, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 BusinessEntityID AS ID, \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Demographics.query('\/')\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM Sales.Store AS Store\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE SalesPersonID = 282\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR XML AUTO, ROOT('StoreSurveys')\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\n\nSELECT @xml<\/pre>\n
At this point, we have the same XML structure as before:<\/p>\n
\"1585-1-3fdaad5a-44e2-499c-b573-c6f6d05ca\"<\/p>\n
Now we will break apart the XML into logical records, again using the XQuery nodes() method:<\/p>\n
-- separate store survey XML records\n;WITH XMLNAMESPACES ('http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/adventure-works\/StoreSurvey' AS ns)\nSELECT C.value('..\/@ID','int') AS BusinessEntityID,\n\u00a0\u00a0 C.value('..\/@Name','varchar(50)') AS StoreName,\n\u00a0\u00a0 C.query('.') AS Demographics\nFROM @xml.nodes('\/StoreSurveys\/Store\/ns:StoreSurvey') AS T(C)\nGO<\/pre>\n
\"1585-1-9ccb272c-899f-4b8d-922d-5c93d5416\"<\/p>\n
The new Demographics XML structure for BusinessEntityID 312<\/strong>:<\/p>\n
\"1585-1-3d41f4de-3b55-423d-8498-545df32c3\"<\/p>\n
Note that we did not<\/strong> use CROSS APPLY this time, since the XML data was in a variable instead of an XML column in a table. Also, we used the ‘.<\/strong>‘, a self::node()<\/a> abbreviation, in the query() method’s path expression – to pull out the store survey data. This indicates that the XML portion should be extracted from the nodes() method’s node set (the ‘<\/strong>Store<\/strong>‘<\/strong> node), whereas a ‘\/<\/strong>‘ root path expression would have caused the XML to be extracted from the root node (thus returning the entire XML structure). Also, notice that we used the parent::node()<\/a> abbreviation ‘..<\/strong>‘ in order to reference attribute values ‘Name<\/strong>‘ and ‘ID<\/strong>‘ that are in the node above<\/strong> the ‘StoreSurvey<\/strong>‘ node (the parent ‘Store<\/strong>‘ node). A variation on the script that does not<\/strong> require a namespace declaration could be written as follows:<\/p>\n
-- separate store survey XML records without namespace declaration\nSELECT C.value('@ID','int') AS BusinessEntityID,\n\u00a0\u00a0 C.value('@Name','varchar(50)') AS StoreName,\n\u00a0\u00a0 C.query('.\/child::node()') AS Demographics\nFROM @xml.nodes('\/StoreSurveys\/Store') AS T(C)\nGO<\/pre>\n
The above script uses the Child<\/a> axis to drill down one level further than the nodes() method’s node set, thereby eliminating the need to reference the ‘StoreSurvey<\/strong>‘ node directly.<\/p>\n
Summary<\/h2>\n
We’ve taken a look at some ways to coerce XML data to fit specific needs. We handled one common request, creating XML instances from existing tables, by using the FOR XML clause – applying appropriate arguments to design the XML structure to fit specific aesthetic requirements. We demonstrated another very common procedure, shredding XML data, by employing the XQuery nodes() method. We saw that multiple XML fragments and instances can also be merged into a single instance by using the FOR XML tools. We then reversed that operation by splitting the XML instance into logical records of XML data.<\/p>\n
Manipulating XML data to fit your needs may take creativity and some experimentation with new tools. We’ve worked out solutions for a few basic problems, but there is more to learn. The techniques that we have introduced here should help to get you started.<\/p>\n<\/div>\n\n\n
\n    
FAQs: Manipulating XML Data in SQL Server<\/h2>\n\n                        
1. How do I convert a SQL Server table to XML?<\/h3>\n            
\n                
Use FOR XML in your SELECT statement. FOR XML AUTO generates a simple XML hierarchy based on the table and column structure. FOR XML PATH gives you explicit control over element names and nesting using column aliases. FOR XML RAW generates a flat attribute-based XML. For nested XML from multiple tables, use FOR XML PATH with nested subqueries or CROSS APPLY. Example: SELECT CustomerID, CustomerName FROM Customers FOR XML PATH(‘Customer’), ROOT(‘Customers’).<\/p>\n            <\/div>\n                    
2. How do I shred XML to rows in SQL Server?<\/h3>\n            
\n                
For SQL Server 2005 and later, use the XQuery nodes() method to shred repeating XML elements into rows: SELECT T.Item.value(‘(Name)[1]’, ‘NVARCHAR(50)’) FROM @xml.nodes(‘\/Root\/Item’) T(Item). For older approaches using sp_xml_preparedocument and OPENXML, the syntax is more verbose but functionally similar. The nodes() method is the preferred modern approach because it avoids the overhead of the sp_xml_preparedocument\/sp_xml_removedocument pair.<\/p>\n            <\/div>\n                    
3. How do I handle XML namespaces in SQL Server XQuery?<\/h3>\n            
\n                
Use the WITH XMLNAMESPACES clause before your SELECT statement to declare namespace prefixes: WITH XMLNAMESPACES(‘http:\/\/example.com\/schema’ AS ns) SELECT @xml.value(‘ns:Root[1]\/ns:Item[1]\/@Name’, ‘NVARCHAR(50)’). Without the namespace declaration, XQuery expressions will not find elements that use namespaces, returning NULL silently. Alternatively, use the wildcard namespace notation *:ElementName if you want to match regardless of namespace.<\/p>\n            <\/div>\n                    
4. What is the difference between FOR XML and FOR JSON in SQL Server?<\/h3>\n            
\n                
FOR XML and FOR JSON are both output modifiers that transform SELECT query results into a structured document format. FOR XML (available since SQL Server 2000) outputs XML. FOR JSON (available from SQL Server 2016) outputs JSON. Both support PATH mode (explicit structure) and AUTO mode (inferred structure). FOR JSON is simpler for most modern data exchange use cases because JSON is lighter-weight. FOR XML remains necessary when the consuming system requires XML specifically, or for use with XQuery operations on the resulting XML.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Manipulate XML data in SQL Server using T-SQL: create XML from tables with FOR XML, shred XML to rows with OPENXML and XQuery, handle namespaces, use the nodes() method, and combine or separate XML instances.…<\/p>\n","protected":false},"author":221920,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[143527],"tags":[4242,4168,4170,5743,4150,5742,5134,4151,4217],"coauthors":[48345],"class_list":["post-1428","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-basics","tag-database","tag-database-administration","tag-node-creation","tag-sql","tag-sql-and-xml","tag-sql-prompt","tag-sql-server","tag-xml"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1428","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221920"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1428"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1428\/revisions"}],"predecessor-version":[{"id":109942,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1428\/revisions\/109942"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1428"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1428"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1428"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1428"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}