Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

XML into SQL Server 2008 R2 Table from VB via Stored Procedure

Posted on 2014-04-11
13
Medium Priority
?
624 Views
Last Modified: 2014-04-14
I have an XML document that I want to post into a SQL Server 2008 R2 Table from VB via Stored Procedure.  The Proc has a parameter field of type XML.  

Commx.Parameters.AddWithValue("@XMLFile", doc)

Open in new window

I use this line where Commx is a declared as
 
 Using Commx As New SqlCommand

Open in new window


The limitations I have is that I can't use LINQ to SQL in this case.
0
Comment
Question by:Alyanto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 39994918
Is you want to convert XML to a table you can use a combination of .nodes and .value functions.   For example:
<products>
   <product id="1" name="Product 1" />
   <product id="2" name="Product 2" />
   <product id="3" name="Product 3" />
   <product id="4" name="Product 4" />
</products>

Open in new window


Then you can get the info with the following code:
SELECT c.value('@id', 'TINYINT') AS Id, c.value('@name', 'VARCHAR(9)') AS Name
FROM @YourXMLParam.nodes('/products/product') AS T(c)

Open in new window

0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 39995065
Are you getting an exception? If so please post the exception and inner exception messages .

Which limitations are you talking about?
0
 
LVL 28

Expert Comment

by:Ark
ID: 39995592
What is a problem with LINQ?
Drag your procedure to the data context designer and it'll generate a method for you. Just call it with your xml as argument.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Author Comment

by:Alyanto
ID: 39995754
The code wilkl be used in an SSIS script and there is a corperate standard that excludes the use of LINQ to SQL
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 39995910
Can you answer the questions  that were posted to you please.
0
 
LVL 1

Author Comment

by:Alyanto
ID: 39995928
I will when I return to work on Monday provide it exactly however the sentiment of the message was that no mapping of type Linq,XML was possible with the parameter.  I did check to see if the Stored Proc's parameter was of the correct name and type XML and they were as expected.
0
 
LVL 1

Author Comment

by:Alyanto
ID: 39998375
The error message is:
No mapping exists from object type System.Xml.Linq.XDocument to a known managed provider native type.
0
 
LVL 1

Author Comment

by:Alyanto
ID: 39998377
My code base, Still needs a a tidy and optimization but works other than the save  to database action

Public Class Branches
    Public Sub New(ByVal BranchNumber As Integer)

        Dim X As New Branch(24)

        Dim oBranches As New Generic.List(Of Branch)

        oBranches.Add(X)

        Dim oDespatch As New Generic.List(Of Despatch)
        oDespatch.AddRange(X.DespatchList)

        Dim oStock As New Generic.List(Of StockItem)
        oStock.AddRange(X.StockList)

        Dim doc = New XDocument()

        Dim Brch As XElement = New XElement("Branch", From b As Branch In oBranches _
                                                      Select New XAttribute("BranchNumber", b.BranchNumber))

        Dim BranchName As String = String.Empty
        Dim ShortNameCode As String = String.Empty
        Dim StoreItemCount As Decimal = oBranches.Count + oDespatch.Count

        For Each b As Branch In oBranches
            BranchName = b.BranchName
            ShortNameCode = b.ShortNameCode

            Dim AttrBranchName As XAttribute = New XAttribute("BranchName", b.BranchName)
            Dim AttrBranchDistrict As XAttribute = New XAttribute("BranchDistrict", b.BranchDistrict)
            Dim AttrBranchRegion As XAttribute = New XAttribute("BranchRegion", b.BranchRegion)
            Dim AttBranchZone As XAttribute = New XAttribute("BranchZone", b.BranchZone)
            Dim AttBranchCountry As XAttribute = New XAttribute("BranchCountry", b.BranchCountry)
            Dim AttBranchCountryNo As XAttribute = New XAttribute("BranchCountryNo", b.BranchCountryNo)

            Brch.Add(AttrBranchName)
            Brch.Add(AttrBranchDistrict)
            Brch.Add(AttrBranchRegion)
            Brch.Add(AttBranchZone)
            Brch.Add(AttBranchCountry)
            Brch.Add(AttBranchCountryNo)
        Next

        Dim Stck As XElement = New XElement("Stock", From s As StockItem In oStock _
                                                     Select New XElement("Item", _
                                                                         New XAttribute("LongCode", s.LongCode), _
                                                                         New XAttribute("ShortCode", s.ShortCode), _
                                                                         New XAttribute("ProductDescription", s.ProductDescription), _
                                                                         New XAttribute("BranchSellingPrice", s.BranchSellingPrice), _
                                                                         New XAttribute("CurrentStockUnits", s.CurrentStockUnits), _
                                                                         New XAttribute("ToPickUnits", s.ToPickUnits), _
                                                                         New XAttribute("AnalysisCode1", s.AnalysisCode1), _
                                                                         New XAttribute("Analysiscode1Description", s.Analysiscode1Description), _
                                                                         New XAttribute("AnalysisCode2", s.AnalysisCode2), _
                                                                         New XAttribute("AnalysisCode2Description", s.AnalysisCode2Description), _
                                                                         New XAttribute("CountryAnalysisCode", s.CountryAnalysisCode), _
                                                                         New XAttribute("CountryAnalysisCodeDescription", s.CountryAnalysisCodeDescription), _
                                                                         New XAttribute("StockHoldingGroup", s.StockHoldingGroup), _
                                                                         New XAttribute("StockHoldingGroupDescription", s.StockHoldingGroupDescription), _
                                                                         New XAttribute("DepartmentName", s.DepartmentName), _
                                                                         New XAttribute("DepartmentNumber", s.DepartmentNumber), _
                                                                         New XAttribute("ClassNumber", s.ClassNumber), _
                                                                         New XAttribute("ClassName", s.ClassName)))

        Dim Desp As XElement = New XElement("Despatches", From d As Despatch In oDespatch _
                                                        Select New XElement("Despatch", _
                                                                            New XAttribute("LongCode", d.LongCode), _
                                                                            New XAttribute("Units", d.Units), _
                                                                            New XAttribute("TransactionType", d.TransactionType), _
                                                                            New XAttribute("Memo", d.Memo), _
                                                                            New XAttribute("OtherBranchNumber", d.OtherBranchNumber), _
                                                                            New XAttribute("TransactionDate", d.TransactionDate)))

        Brch.Add(Stck)
        Brch.Add(Desp)

        Dim emp = New XElement("Branches")
        emp.Add(Brch)

        doc.Add(emp)


        Dim FileName As String = String.Empty

        FileName = String.Format("{0}{1}_{2}.XML", BranchNumber.ToString.PadLeft(4, "0"), BranchName.Replace(" ", ""), Date.Today.ToShortDateString.Replace("/", ""))




        'Dim Dt As New DataTable
        'Dim dtAdapter As SqlDataAdapter
        Try
            Using Connx As New SqlConnection("Server=Dashboard;Database=PASTIS;Trusted_Connection=True;")
                Using Commx As New SqlCommand
                    Commx.CommandType = CommandType.StoredProcedure
                    Commx.CommandText = "PI_InsertTransmission"
                    Commx.Parameters.AddWithValue("@BranchName", BranchName)
                    Commx.Parameters.AddWithValue("@BranchNumber", BranchNumber)
                    Commx.Parameters.AddWithValue("@ShortNameCode", ShortNameCode)
                    Commx.Parameters.AddWithValue("@XMLFile", doc)
                    Commx.Parameters.AddWithValue("@StoreItemCount", StoreItemCount)
                    Commx.Parameters.AddWithValue("@FileName", FileName)
                    Commx.Connection = Connx
                    Connx.Open()
                    Commx.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As Exception
            Debug.Print(ex.Message)
        End Try




        'PI_InsertTransmission @BranchName varchar(20),@BranchNumber int, @ShortNameCode varchar(4), @XMLFile xml, @StoreItemCount decimal(18,2),@FileName varchar(200)

        'doc.Save("C:\Test\text07.xml")

    End Sub

Open in new window

0
 
LVL 28

Expert Comment

by:Ark
ID: 39998381
What SQL type is @XMLFile? If it's ntext/nvarchar/nchar etc - use
Commx.Parameters.AddWithValue("@XMLFile", doc.ToString)
0
 
LVL 1

Author Comment

by:Alyanto
ID: 39998386
CREATE PROCEDURE PI_InsertTransmission
  @BranchName varchar(20),
  @BranchNumber int,
  @ShortNameCode varchar(4),
  @XMLFile xml,
  @StoreItemCount decimal(18,2),
  @FileName varchar(200)
0
 
LVL 28

Accepted Solution

by:
Ark earned 2000 total points
ID: 39998399
According to MSDN
For SqlDbType Xml enumeration values, you can use a string, an XML value, an XmlReader derived type instance, or a SqlXml object.
So you can use doc.ToString
0
 
LVL 28

Expert Comment

by:Ark
ID: 39998407
It can be
Commx.Parameters.AddWithValue("@XMLFile", doc.ToString)
or
Commx.Parameters.Add("@XMLFile",  SqlDbType.Xml).Value = doc.ToString
1
 
LVL 1

Author Closing Comment

by:Alyanto
ID: 39998415
Drum roll, the award goes to Arc.  Many thanks :)

/Aly
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question