Adding namespace to created XML file

Question

Adding namespace to created XML file

Pro100

Right there with Babe

Points: 769
More actions
May 19, 2016 at 6:51 am

#104326

Hello guys,
Iv created a xml file using SQL server and SSIS, after executing query i use script in c# to add something to it and create XML file.
string Sth = Dts.Variables["variable].Value.ToString().Replace("<ROOT>", "<?xml version=\"1.0\" encoding=\"UTF-8\"?>").Replace("</ROOT>", "");
Sth = Sth.Replace("<ROOT>", @"<ROOT xmlns:ns0=""namespace"">");
XmlDocument xdoc = new XmlDocument();
xdoc.LoadXml(Data.ToString());
string outputFile = Dts.Variables["FilePath"].Value.ToString() + "\\" + Dts.Variables["file"].Value.ToString();
xdoc.Save(outputFile);
Dts.TaskResult = (int)ScriptResults.Success;
Most of it its copied from some forum, and changed then. I had some problem becouse its generate me :
<?xml version="1.0" encoding="UTF-8"?>
<name xmlns:ns0="namespace">
<field1>Something</field1>
<field2>test</field2>
<field3>test2</field3>
And i want this :
<?xml version="1.0" encoding="UTF-8"?>
<ns0:name xmlns:ns0="namespace">
<ns0:field1>Something</ns0:field1>
<ns0:field2>test</ns0:field2>
<ns0:field3>test2</ns0:field3>
All of these "variables" has been take from SQL.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Russel Loski SSCertifiable Points: 7059 More actions · Answer 1

A couple thoughts. Where does the XML come from? If it comes from SQL, it would be easy to change the FOR XML to output the namespace.

I would try this:

\

Sth = Sth.Replace("<ROOT>", @"<ROOT xmlns=""namespace"">");

Any decent XML parser will read it the same as the output you have.

Russel Loski, MCSE Business Intelligence, Data Platform

Pro100 Right there with Babe Points: 769 More actions · Answer 2

Thanks for fast answer, the XML is generated by SQL and then script in c# save it and add some things.

As you can see Iv already try this, its generated the 1st lane but it doesnt gave me the "prefix" in these lanes then.

Also i forgot to add this is previous post but i had one more problem, i want to do this :

<?xml version="1.0" encoding="UTF-8"?>

<nso:name xmlns:ns0="namespace"

xmlns:xis="anotherOne"

xmlns="andAnotherOne" >

<ns0:field1>Something</ns0:field1>

<ns0:field2>test</ns0:field2>

<ns0:field3>test2</ns0:field3>

Is it possible to do something like this?

So what you saying is :

<field1>Something</field1> == <ns0:field1>Something</ns0:field1> ?

Russel Loski SSCertifiable Points: 7059 More actions · Answer 3

Do you have a reason to use ns0 for the prefix? Are you sending data to a tool that does not use a proper XML parser?

I have found it challenging to change namespaces. It can be done, but it requires XSLT.

If you can control the SQL that generates the XML, I would try to generate the XML with the proper namespaces in the SQL.

Russel Loski, MCSE Business Intelligence, Data Platform

Pro100 Right there with Babe Points: 769 More actions · Answer 4

I will send created xml to another person who gave me a example how this xml should look like and this prefix was in it so i think i need this prefix in my generated xml.

Russel Loski SSCertifiable Points: 7059 More actions · Answer 5

Then it is time to learn XSLT.

Russel Loski, MCSE Business Intelligence, Data Platform

Pro100 Right there with Babe Points: 769 More actions · Answer 6

Isnt there any way to add it in my SQL procedure or c# script?

Russel Loski SSCertifiable Points: 7059 More actions · Answer 7

Pro100 (5/19/2016)
Isnt there any way to add it in my SQL procedure or c# script?

Sorry, since you hadn't responded to my questions about putting it into the SQL, I assumed that that was not a possibility.

What is your SQL Script?

Russel Loski, MCSE Business Intelligence, Data Platform

Russel Loski SSCertifiable Points: 7059 More actions · Answer 8

You would just need something like this:

With XMLNamespaces ('namespace' as ns0)

select col as [ns0:col]

from tbl

for xml path

Russel Loski, MCSE Business Intelligence, Data Platform

Pro100 Right there with Babe Points: 769 More actions · Answer 9

I will check it at the evening when i will have Access to the PC

Could you just show me an exmaple?

Like i had this :

SELECT (SELECT 'test' AS whoSend,

'123' AS Id,

'321' AS test,

'PL' AS where,

'PL' AS from,

'Warsaw' AS postOffice,

(SELECT @datum ) as "date",

(SELECT 1 AS constValue,

(Select (@value)) as weight,

(SELECT count(*)

) AS all,

'' as number,

'PL' as sendFrom,

'type' as itemType

FROM database

FOR XML PATH ('testtest'), TYPE)

FOR XML PATH (''), ROOT ('something'));

And can you show me how should i do it?