Edit

Columns with a name specified as a wildcard character

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

If the column name specified is a wildcard character (*), the content of that column is inserted as if there's no column name specified. If this column is a non-xml type column, the column content is inserted as a text node, as shown in the following example:

Note

In Azure SQL Database and SQL database in Microsoft Fabric, the USE statement isn't available. Instead, connect directly to intended database and run the rest of the code sample.

USE AdventureWorks2022;
GO
SELECT E.BusinessEntityID "@EmpID",
       FirstName "*",
       MiddleName "*",
       LastName "*"
FROM   HumanResources.Employee AS E
  INNER JOIN Person.Person AS P
    ON E.BusinessEntityID = P.BusinessEntityID
WHERE E.BusinessEntityID=1
FOR XML PATH;

This is the result:

<row EmpID="1">KenJSánchez</row>

If the column is of xml type, the corresponding XML tree is inserted. For example, the following query specifies "*" for the column name that contains the XML returned by the XQuery against the Instructions column.

SELECT
       ProductModelID,
       Name,
       Instructions.query('declare namespace MI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
                /MI:root/MI:Location
              ') as "*"
FROM Production.ProductModel
WHERE ProductModelID=7
FOR XML PATH;

This is the result. The XML returned by XQuery is inserted without a wrapping element.

<row>
  <ProductModelID>7</ProductModelID>
  <Name>HL Touring Frame</Name>
  <MI:Location LocationID="10">...</MI:Location>
  <MI:Location LocationID="20">...</MI:Location>
  ...
</row>

Related content

  • Last updated on