Hi there,
I need help urgently!
I need to add xml declaration and namespace as shown below:
<?xml version="1.0" encoding="UTF-8" ?>
xmlns="http://online.ogcbuyingsolutions.gov.uk/schemas/products" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://online.ogcbuyingsolutions.gov.uk/schemas/products products_1_17.xsd">
I have written the stored proc in SQL 2008 to generate an xml output using thefor xml explicit and that is all fine. The problem is how do I add the xml declaration and namespace. It seems you can add this using the other type for xml auto, raw etc but not for explicit.
The namespace I have tried to add as an attribute to the root tag but I can't seem to add all of it as the quotes are automatically place from the beginning to the end whereas I want them in three different places - see above for reference.
Please can someone advise how to resolve this issue or a way of getting around this.
Also I have placed my sql code for reference too
[
SELECT 1 AS Tag,
NULL AS Parent,
@NameSpace AS [products!1!xmlns],
NULL AS [login!2!username!element],
NULL AS [login!2!password!element],
NULL AS [login!2!supplier_tag!element],
NULL AS [actions!3!products],
NULL AS [delete!4],
NULL AS [code!5!type],
NULL AS [code!5],
NULL AS [product!6!product_status],
null AS [product!6!ItemID!element],
NULL AS [product!6!contract_code!element],
NULL AS [product!6!unspsc_code!element],
NULL AS [product!6!name!element],
NULL AS [product!6!summary!element],
NULL AS [product!6!description!element],
NULL AS [product!6!manufacturer!element],
NULL AS [product!6!url!element],
NULL AS [product!6!vat_band!element],
NULL AS [product!6!currency_code!element],
NULL AS [variants!7],
null AS [variant!8!code!element],
NULL AS [variant!8!manufacturer_code!element],
NULL AS [variant!8!weighting!element],
NULL AS [variant!8!ogcbs_price!element],
NULL AS [variant!8!price_unit!element],
NULL AS [variant!8!special_offer!element],
NULL AS [variant!8!image!element],
NULL AS [variant!8!delivery_model!element]
UNION ALL
SELECT
2 AS TAG,
1 AS Parent,
NULL,
Fixed_UserName,
Fixed_Password,
FixedSupplier_Tag,
NULL,
null,
null,
null,
NULL,
NULL,
NULL,
NULL,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL
FROM #LoginInfo
UNION ALL
SELECT
3 AS TAG,
1 AS Parent,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
null,
null,
NULL,
NULL,
NULL,
NULL,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL
UNION ALL
SELECT
4 AS TAG,
3 AS Parent,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
null,
null,
NULL,
NULL,
NULL,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL
UNION ALL
SELECT
5 AS TAG,
4 AS Parent,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'product',
ItemID,
null,
NULL,
NULL,
NULL,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL
FROM #DeleteProducts
UNION ALL
SELECT DISTINCT
6 AS Tag,
1 AS Parent,
null,
null,
null,
null,
null,
null,
null,
null,
a.itemstatustxt,
a.ItemID AS [product!6!ItemID!element],
null,
NULL,
a.name,
null,
null,
a.manufacturer,
a.url,
NULL,
a.currencycode,
null,
null,
null,
null,
null,
null,
NULL,
NULL,
NULL
FROM #ActiveProducts a
inner join #Product_Variants b on a.ItemID = b.itemid
UNION ALL
SELECT DISTINCT
7 AS TAG,
6 AS Parent,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
null,
NULL,
a.ItemID,
null,
NULL,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL,
null,
null,
null,
null,
NULL,
NULL,
NULL,
null
FROM #ActiveProducts a
inner join #Product_Variants b on a.ItemID = b.itemid
UNION ALL
SELECT
8 AS Tag,
7 AS Parent,
NULL,
null,
null,
null,
NULL,
NULL,
null,
null,
NULL,
a.itemid AS [product!6!ItemID!element],
NULL,
null,
null,
null,
null,
NULL,
NULL,
NULL,
NULL,
null,
b.code AS [variant!8!code!element],
b.manufacturercode,
b.weighting,
null,
b.priceunit,
NULL,
b.itemimage,
NULL
FROM #ActiveProducts a
inner join #Product_Variants b on a.ItemID = b.itemid
order by [product!6!ItemID!element],[variant!8!code!element], Tag
FOR XML EXPLICIT
]
Many thanks in advance
Regards,
Shaziya Chughtai