Once i was working with xml sql query i had problem in order to create custom formatted xml, now its easy form me. The given bellow example explain easily uses of formatted query in order to generate custom xml.
Example
SELECT * FROM salesorder
produces::
ordernumber customername customerstreet
----------- ------------ --------------
1 parker first av
2 lesley sec av
If we want the resultset to be XML, we add the FOR XML AUTO statement:
SELECT * FROM salesorder FOR XML AUTO
which produces::
<salesorder ordernumber="1" customername="parker" customerstreet="first av"/>
<salesorder ordernumber="2" customername="lesley" customerstreet="sec av"/>
But now, you have the fields as attributes. Most of the time, you will want them as elements. To do this, you add the ELEMENTS parameter:
Example
SELECT * FROM salesorder FOR XML AUTO, ELEMENTS
Which produces::
<salesorder>
<ordernumber>1</ordernumber>
<customername>parker</customername>>
If you want to change the 'salesorder' tag, you can use:
Example
SELECT * FROM salesorder AS niceorder FOR XML AUTO, ELEMENTS
Which produces:
Hide Copy Code
<niceorder>
<ordernumber>1</ordernumber>
<customername>parker</customername>>
And of course, this trick also works for column names:
Example
SELECT ordernumber AS order_no FROM salesorder WHERE ordernumber = 1 FOR XML AUTO, ELEMENTS
Which produces:
Hide Copy Code
<salesorder>
<order_no>1</order_no >
</salesorder>
What if you want to add additional tags or add nodes? For instance, add 'customer' tags around customer information? This proves to be difficult with FOR XML AUTO. A possible solution is a SELF JOIN (join to the same table), but I found something easier. After much fiddling and tinkering, we use a subquery and slightly abuse the FOR XML PATH command, like this:
Example
SELECT
ordernumber,
(SELECT customername ,
customerstreet FOR XML PATH(''),
TYPE, ELEMENTS)
as customer
FROM
salesorder
FOR XML AUTO, ELEMENTS
Which produces:
<salesorder>
<ordernumber>1</ordernumber>
<customer>
<customername>parker</customername>
<customerstreet>first av</customerstreet>
</customer>
</salesorder>
<salesorder>
<ordernumber>2</ordernumber>
<customer>
<customername>lesley</customername>
<customerstreet>sec av</customerstreet>
</customer>
</salesorder>
Notice the use of the additional 'TYPE' parameter. This will ensure the result of the subquery is returned as an XML type (as part of the the whole XML type result), rather than the NVARCHAR(MAX) type. This neat little trick also works if you want to add surrounding tags to your complete result:
Example
SELECT(
SELECT
customername
FROM
salesorder
FOR XML AUTO, TYPE, ELEMENTS
) AS orderrequest FOR XML PATH(''), TYPE, ELEMENTS
Which produces:
<orderrequest>
<salesorder>
<customername>parker</customername>
</salesorder>
<salesorder>
<customername>lesley</customername>
</salesorder>
</orderrequest>
Why don't we use the standard FOR XML AUTO in the subquery? Try it, it will generate an error. You can only use FOR XML AUTO in a subquery when the subquery is a query on an actual table (which the above obviously isn't).
The subquery construction is the way to go if you want full control over the produced XML output. Let's say we want, per order, the customer name and all the items belonging to the order. For this, you use correlated subqueries like this:
Example
SELECT
customername ,
(SELECT * FROM item WHERE item.ordernumber =
salesorder.ordernumber FOR XML AUTO, TYPE, ELEMENTS)
FROM
salesorder
FOR XML AUTO, ELEMENTS
Which produces:
<salesorder>
<customername>parker</customername>
<item>
<itemnumber>10</itemnumber>
<description>pen</description>
<ordernumber>1</ordernumber>
</item>
<item>
<itemnumber>11</itemnumber>
<description>paper</description>
<ordernumber>1</ordernumber>
</item>
</salesorder>
When using correlated subqueries, you can use the regular FOR XML AUTO, TYPE, ELEMENTS statement. If you want surrounding tags around the 'items', just add AS after the subquery, like this:
Example
SELECT
customername ,
(SELECT * FROM item WHERE item.ordernumber =
salesorder.ordernumber FOR XML AUTO, TYPE, ELEMENTS)
AS orderitems
FROM
salesorder
FOR XML AUTO, ELEMENTS
Which produces:
<salesorder>
<customername>parker</customername>
<orderitems>
<item>
<itemnumber>10</itemnumber>
<description>pen</description>
<ordernumber>1</ordernumber>
</item>
<item>
<itemnumber>11</itemnumber>
<description>paper</description>
<ordernumber>1</ordernumber>
</item>
</orderitems>
</salesorder>
Why don't we just simply join the item table to the order table? This will sometimes lead to unwanted and unpredictable behaviour when it comes to the layout of the produced XML. For instance:
Example
SELECT
item.description,
salesorder.customername
FROM
salesorder
INNER JOIN item ON item.ordernumber = salesorder.ordernumber
FOR XML AUTO, ELEMENTS
Will produce garbage like this:
<item>
<description>pen</description>
<salesorder>
<customername>parker</customername>
</salesorder>
</item>
<item>
<description>paper</description>
<salesorder>
<customername>parker</customername>
</salesorder>
</item>
Also, you will have a hard time adding surrounding tags when using joins. So, for most controls, when using FOR XML AUTO, use (correlated) subqueries.
Related Error:
Msg 6852, Level 16, State 1, Line 2
Attribute-centric column '@copyrights' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.
This is the common error when you misplace the attribute position.
Like in next example
SELECT Employee.EmployeeID AS '@EmployeeID', -- right way to call
Contact.FirstName AS 'FirstName',
Contact.LastName AS 'LastName',
AddressLine1 AS 'Address/AddressLine1',
AddressLine2 AS 'Address/AddressLine2',
City AS 'Address/City',
StateProvinceCode AS 'Address/StateOrProvince',
PostalCode AS 'Address/PostalCode'
FROM HumanResources.Employee Employee(NOLOCK)
JOIN Person.Contact Contact (NOLOCK)
ON Employee.ContactID = Contact.ContactID
JOIN HumanResources.EmployeeAddress ea(NOLOCK)
ON Employee.EmployeeID = ea.EmployeeID
JOIN Person.Address Address (NOLOCK)
ON ea.AddressID = Address.AddressID
JOIN Person.StateProvince s (NOLOCK)
ON Address.StateProvinceID = s.StateProvinceID
FOR XML PATH ('Employee'), ROOT('Employees')
Result:
<Employees>
<Employee EmployeeID="1">
<FirstName>Guy</FirstName>
<LastName>Gilbert</LastName>
<Address>
<AddressLine1>7726 Driftwood Drive</AddressLine1>
<City>Monroe</City>
<StateOrProvince>WA </StateOrProvince>
<PostalCode>98272</PostalCode>
</Address>
</Employee>
<Employee EmployeeID="2">
<FirstName>Kevin</FirstName>
<LastName>Brown</LastName>
<Address>
<AddressLine1>7883 Missing Canyon Court</AddressLine1>
<City>Everett</City>
<StateOrProvince>WA </StateOrProvince>
<PostalCode>98201</PostalCode>
</Address>
</Employee>
...
</Employees>
SELECT
Contact.FirstName AS 'FirstName',
Contact.LastName AS 'LastName',
AddressLine1 AS 'Address/AddressLine1',
AddressLine2 AS 'Address/AddressLine2',
City AS 'Address/City',
StateProvinceCode AS 'Address/StateOrProvince',
PostalCode AS 'Address/PostalCode',
Employee.EmployeeID AS '@EmployeeID', -- wrong way to call
FROM HumanResources.Employee Employee(NOLOCK)
JOIN Person.Contact Contact (NOLOCK)
ON Employee.ContactID = Contact.ContactID
JOIN HumanResources.EmployeeAddress ea(NOLOCK)
ON Employee.EmployeeID = ea.EmployeeID
JOIN Person.Address Address (NOLOCK)
ON ea.AddressID = Address.AddressID
JOIN Person.StateProvince s (NOLOCK)
ON Address.StateProvinceID = s.StateProvinceID
FOR XML PATH ('Employee'), ROOT('Employees')
Given query produce
Attribute-centric column '@copyrights' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.
Example
SELECT * FROM salesorder
produces::
ordernumber customername customerstreet
----------- ------------ --------------
1 parker first av
2 lesley sec av
If we want the resultset to be XML, we add the FOR XML AUTO statement:
SELECT * FROM salesorder FOR XML AUTO
which produces::
<salesorder ordernumber="1" customername="parker" customerstreet="first av"/>
<salesorder ordernumber="2" customername="lesley" customerstreet="sec av"/>
But now, you have the fields as attributes. Most of the time, you will want them as elements. To do this, you add the ELEMENTS parameter:
Example
SELECT * FROM salesorder FOR XML AUTO, ELEMENTS
Which produces::
<salesorder>
<ordernumber>1</ordernumber>
<customername>parker</customername>>
If you want to change the 'salesorder' tag, you can use:
Example
SELECT * FROM salesorder AS niceorder FOR XML AUTO, ELEMENTS
Which produces:
Hide Copy Code
<niceorder>
<ordernumber>1</ordernumber>
<customername>parker</customername>>
And of course, this trick also works for column names:
Example
SELECT ordernumber AS order_no FROM salesorder WHERE ordernumber = 1 FOR XML AUTO, ELEMENTS
Which produces:
Hide Copy Code
<salesorder>
<order_no>1</order_no >
</salesorder>
What if you want to add additional tags or add nodes? For instance, add 'customer' tags around customer information? This proves to be difficult with FOR XML AUTO. A possible solution is a SELF JOIN (join to the same table), but I found something easier. After much fiddling and tinkering, we use a subquery and slightly abuse the FOR XML PATH command, like this:
Example
SELECT
ordernumber,
(SELECT customername ,
customerstreet FOR XML PATH(''),
TYPE, ELEMENTS)
as customer
FROM
salesorder
FOR XML AUTO, ELEMENTS
Which produces:
<salesorder>
<ordernumber>1</ordernumber>
<customer>
<customername>parker</customername>
<customerstreet>first av</customerstreet>
</customer>
</salesorder>
<salesorder>
<ordernumber>2</ordernumber>
<customer>
<customername>lesley</customername>
<customerstreet>sec av</customerstreet>
</customer>
</salesorder>
Notice the use of the additional 'TYPE' parameter. This will ensure the result of the subquery is returned as an XML type (as part of the the whole XML type result), rather than the NVARCHAR(MAX) type. This neat little trick also works if you want to add surrounding tags to your complete result:
Example
SELECT(
SELECT
customername
FROM
salesorder
FOR XML AUTO, TYPE, ELEMENTS
) AS orderrequest FOR XML PATH(''), TYPE, ELEMENTS
Which produces:
<orderrequest>
<salesorder>
<customername>parker</customername>
</salesorder>
<salesorder>
<customername>lesley</customername>
</salesorder>
</orderrequest>
Why don't we use the standard FOR XML AUTO in the subquery? Try it, it will generate an error. You can only use FOR XML AUTO in a subquery when the subquery is a query on an actual table (which the above obviously isn't).
The subquery construction is the way to go if you want full control over the produced XML output. Let's say we want, per order, the customer name and all the items belonging to the order. For this, you use correlated subqueries like this:
Example
SELECT
customername ,
(SELECT * FROM item WHERE item.ordernumber =
salesorder.ordernumber FOR XML AUTO, TYPE, ELEMENTS)
FROM
salesorder
FOR XML AUTO, ELEMENTS
Which produces:
<salesorder>
<customername>parker</customername>
<item>
<itemnumber>10</itemnumber>
<description>pen</description>
<ordernumber>1</ordernumber>
</item>
<item>
<itemnumber>11</itemnumber>
<description>paper</description>
<ordernumber>1</ordernumber>
</item>
</salesorder>
When using correlated subqueries, you can use the regular FOR XML AUTO, TYPE, ELEMENTS statement. If you want surrounding tags around the 'items', just add AS after the subquery, like this:
Example
SELECT
customername ,
(SELECT * FROM item WHERE item.ordernumber =
salesorder.ordernumber FOR XML AUTO, TYPE, ELEMENTS)
AS orderitems
FROM
salesorder
FOR XML AUTO, ELEMENTS
Which produces:
<salesorder>
<customername>parker</customername>
<orderitems>
<item>
<itemnumber>10</itemnumber>
<description>pen</description>
<ordernumber>1</ordernumber>
</item>
<item>
<itemnumber>11</itemnumber>
<description>paper</description>
<ordernumber>1</ordernumber>
</item>
</orderitems>
</salesorder>
Why don't we just simply join the item table to the order table? This will sometimes lead to unwanted and unpredictable behaviour when it comes to the layout of the produced XML. For instance:
Example
SELECT
item.description,
salesorder.customername
FROM
salesorder
INNER JOIN item ON item.ordernumber = salesorder.ordernumber
FOR XML AUTO, ELEMENTS
Will produce garbage like this:
<item>
<description>pen</description>
<salesorder>
<customername>parker</customername>
</salesorder>
</item>
<item>
<description>paper</description>
<salesorder>
<customername>parker</customername>
</salesorder>
</item>
Also, you will have a hard time adding surrounding tags when using joins. So, for most controls, when using FOR XML AUTO, use (correlated) subqueries.
Related Error:
Msg 6852, Level 16, State 1, Line 2
Attribute-centric column '@copyrights' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.
This is the common error when you misplace the attribute position.
Like in next example
SELECT Employee.EmployeeID AS '@EmployeeID', -- right way to call
Contact.FirstName AS 'FirstName',
Contact.LastName AS 'LastName',
AddressLine1 AS 'Address/AddressLine1',
AddressLine2 AS 'Address/AddressLine2',
City AS 'Address/City',
StateProvinceCode AS 'Address/StateOrProvince',
PostalCode AS 'Address/PostalCode'
FROM HumanResources.Employee Employee(NOLOCK)
JOIN Person.Contact Contact (NOLOCK)
ON Employee.ContactID = Contact.ContactID
JOIN HumanResources.EmployeeAddress ea(NOLOCK)
ON Employee.EmployeeID = ea.EmployeeID
JOIN Person.Address Address (NOLOCK)
ON ea.AddressID = Address.AddressID
JOIN Person.StateProvince s (NOLOCK)
ON Address.StateProvinceID = s.StateProvinceID
FOR XML PATH ('Employee'), ROOT('Employees')
Result:
<Employees>
<Employee EmployeeID="1">
<FirstName>Guy</FirstName>
<LastName>Gilbert</LastName>
<Address>
<AddressLine1>7726 Driftwood Drive</AddressLine1>
<City>Monroe</City>
<StateOrProvince>WA </StateOrProvince>
<PostalCode>98272</PostalCode>
</Address>
</Employee>
<Employee EmployeeID="2">
<FirstName>Kevin</FirstName>
<LastName>Brown</LastName>
<Address>
<AddressLine1>7883 Missing Canyon Court</AddressLine1>
<City>Everett</City>
<StateOrProvince>WA </StateOrProvince>
<PostalCode>98201</PostalCode>
</Address>
</Employee>
...
</Employees>
SELECT
Contact.FirstName AS 'FirstName',
Contact.LastName AS 'LastName',
AddressLine1 AS 'Address/AddressLine1',
AddressLine2 AS 'Address/AddressLine2',
City AS 'Address/City',
StateProvinceCode AS 'Address/StateOrProvince',
PostalCode AS 'Address/PostalCode',
Employee.EmployeeID AS '@EmployeeID', -- wrong way to call
FROM HumanResources.Employee Employee(NOLOCK)
JOIN Person.Contact Contact (NOLOCK)
ON Employee.ContactID = Contact.ContactID
JOIN HumanResources.EmployeeAddress ea(NOLOCK)
ON Employee.EmployeeID = ea.EmployeeID
JOIN Person.Address Address (NOLOCK)
ON ea.AddressID = Address.AddressID
JOIN Person.StateProvince s (NOLOCK)
ON Address.StateProvinceID = s.StateProvinceID
FOR XML PATH ('Employee'), ROOT('Employees')
Given query produce
Attribute-centric column '@copyrights' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.