CODEWITHSUNDEEP
c#sqlsql-serverxmlinsert
Leehbi
Leehbi

Reputation: 779

Insert piped SQL data into XML

I need to push the results from SQL Server query into an XML document I have currently have stored in string labelled xmlpush.

The SQL data is raw text data delimited with a pipe but with no xml tags. I can bring this out of SQL into a DataSet without the pipes if necessary.

What is the best way of inserting it into the XML string?

The XML is like something below. I need to load the header into a separate tag named Header and then each row into a tag named row. 

<?xml version='1.0' encoding='UTF-8'?>
<call method="load">
<credentials login="sampleuser"/>
<importDataOptions jobsize="false"/>
<version name="12" isDefault="false" />
<rowData>
<header>1st row from the data set</header>
<rows>
<row>2nd row from dataset here</row>
<row>3rd row from the dataset here</row>
</rows>
</rowData>
</call>

I know nothing about XSLT and a little about XPATH.

My current thinking is to set up a loop to read each line and wrap it with the tags before pushing to a stack and then join up the xml with something like 

begin + stack + end

I'd appreciate any pointers on this? I'm using c#. The dataset can come in around 300-700 rows.

Thanks in advance.

Upvotes: 1

Views: 136

Answers (2)

kchang
kchang

Reputation: 91

SqlServer has a FOR XML option that sounds like it can do what you want:

http://technet.microsoft.com/en-us/library/ms178107.aspx

Upvotes: 1

user2320724
user2320724

Reputation: 686

Basically it looks like you are just building an XML string, in the format that you specified above, and populating certain tags with content from your query result rows.

Pseudo-code:

var myXML = @"<?xml version='1.0' encoding='UTF-8'?>
<call method="load">
<credentials login="sampleuser"/>
<importDataOptions jobsize="false"/>
<version name="12" isDefault="false" />
<rowData>";

var firstRow = true;

while(dataset.hasrows){
    if(firstRow)
    {
        firstRow = false;
        myXML+= "<header>" + 1st row from the data set + "</header><rows>";
    }
    else
    {
        myXML+= "<row>"+ Nth row from dataset here+"</row>";
    }
}

myXML += @"</rows>
</rowData>
</call>";

It doesn't really matter how many rows you have, your XML string will just be longer. Afterwards you can create an XML file from that string. Finally, regarding XSLT, it takes XML as input as far as I know, so it's not needed for building your XML.

Upvotes: 1

Related Questions