Question XML for SQL Stored Procedure

[bcorbett]

Let me start out by saying that i am XML stupid!
I'm not even sure I'm asking this correctly but here it goes....

I have a Stored procedure that will look similar to the following:

Declare @RegionList varchar(1000)
DECLARE @DocHandle int

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @RegionList

	select * from county 
	 join  m_CountyRegion on county.CountyID =    m_CountyRegion.CountyID
	 JOIN  OPENXML (@DocHandle, '/NewDataSet/Region',1) WITH (RegionID  int) AS x
		  ON m_CountyRegion.RegionID = x.RegionID

	EXEC sp_xml_removedocument @DocHandle

I have VB.net 2005 code that passes the XML parameter to the Sproc
using the "ds.getXML"

Dim xmloperator As String = ds.GetXml.ToString 'this is passed to the SPROC

The problem that I am having, is the XML layout that is produced by the vb.net does not match the XML layout that The SPROC expects

VB XML

<NewDataSet>
  <Region>
    <RegionID>9</RegionID>
  </Region>
  <Region>
    <RegionID>10</RegionID>
    </Region>
</NewDataSet>

SQL XML

<ROOT>
   <Reg RegionID = "9"/> 
   <Reg RegionID = "10"/>		
</ROOT>

Is there an easy way to convert the vb.net to the what the SQL is Expecting?

 

[cjard]

Yes, but you should have posted your question in the XML forum; this is nothing to do with databases or stored procedures.

You'r basically looking to transform XML from one representation to another, which you'd use an XSLT for. Take a look at CodeGuru: XSLT Tutorial for starters and seek further help in the XML forum where the gurus have a deeper knowledge of XML

 

[bcorbett]

Thanks for moving my thread.
I looked at the link you provided.
I'm not trying to create a XML file I am trying to write the xml to a varible that I then pass to a Store Procedure.

How do I run XSLT against a variable in vb.net?

Again I'm a little over my head with XML more with XSLT. So baby step it for me. Thanks.

 

[cjard]

I'm not trying to create a XML file

I disagree. If by "file" you mean a stream of bytes stored on a long term storage device such as a hard disk then no, youre not trying to create a file, but if you define a file as a stream of bytes stored in a computer then the XML present in your memory variable is a file like any other.

I am trying to write the xml to a varible that I then pass to a Store Procedure.

Er.. yes.. GetXml gives you XML that you will then transform, and pass to the SP

How do I run XSLT against a variable in vb.net?

XslCompiledTransform (for example) has a Transform method that has an overload taking an XmlReader and an XmlWriter. XmlReader can read from a string and XmlWriter can write to a StringBuilder. No writing to hard disk is neceessary

 

[bcorbett]

Thanks for your help! I converted the C# code that was on that tutorial that you pointed me too. I tweeked it a little bit and it does what I want it to do. It Creates an XML doc from a Dataset and then converts the Format. Now I just need to pass the stored procedure the String and I'm Done! Heres the code:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'Create Dataset and Populate String Variable
        Dim ds As DataSet = New DataSet
        ds = GetDataSet("Select RegionID from Region where RegionID in (9,10)")

        Dim swBefore As New System.IO.StringWriter
        ds.WriteXml(swBefore, XmlWriteMode.IgnoreSchema)

        Me.TextBox1.Text = swBefore.ToString '<--Before

        'Load Doc and Run against XSLT File
        Dim xmldoc As New XmlDocument
        xmldoc.LoadXml(swBefore.ToString)
        Dim xslt As XslTransform = New XslTransform()
        xslt.Load("C:\Style.xslt")

        'Output to stringWriter
        Dim swAfter As New System.IO.StringWriter
        Dim writer As XmlTextWriter = New XmlTextWriter(swAfter)
        writer.Formatting = Formatting.Indented
        xslt.Transform(xmldoc, Nothing, writer, Nothing)

        Me.TextBox2.Text = swAfter.ToString '<--After

       
    End Sub

 

[cjard]

If youre after help for calling a store prcedure and passing a string.. that would be a question for the ado.net forum

 

[bcorbett]

If youre after help for calling a store prcedure and passing a string.. that would be a question for the ado.net forum

Nah, that I know how to do...LOL