importing XML doc into SQL Server

**Christoph Schittko [MVP]** · Nov 11 '05, 10:46 PM

Re: importing XML doc into SQL Server

Christine,

Take a look at SQLXML [0]. You can import the XML data either via
Updategrams or via Bulkload (available through COM interop)

I hope this link is getting you started. Feel free to come back once you
have more questions.

--
HTH
Christoph Schittko [MVP]
Software Architect, .NET Mentor
[0]

http://msdn.microsoft.com/library/default.asp?url=/nhp/default.asp?contentid=28001300

"Christine Mccormick" <cmcormick@eart hlink.net> wrote in message
news:tF0Ma.7217 1$Io.6775986@ne wsread2.prod.it d.earthlink.net ...[color=blue]
> Hello, I am trying to import an XML document into SQL Server. I have tried
> DTS, bulk loading into SQL server and anything else I can think of...
>
> The only thing that has worked slightly is :
> Dim objSQLConn As New SqlConnection(C onstants.Connec tionString)
>
> Dim objAdapter As SqlDataAdapter
>
> Dim objDataRow, objDBRow As DataRow
>
> Dim objDSXML As New DataSet()
>
> Dim child As Xml.XmlNode
>
> Dim objDSDBTable As New DataSet("tblSta ndardstest")
>
> Dim ObjCmdBuilder As SqlCommandBuild er
>
>[/color]
objDSXML.ReadXm l("c:/inetpub/wwwroot/olpt7/ACH_STATE_SUBJE CT_Ohio_2001_En gli[color=blue]
> sh.xml")
>
> objSQLConn.Open ()
>
> objAdapter = New SqlDataAdapter( "SELECT Benchmark, Grade,RefNum FROM
> Standards", objSQLConn)objA dapter.Fill(obj DSDBTable, "tblStandardste st")
>
> For Each objDataRow In objDSXML.Tables ("ACH_BENCHMARK ").Rows
>
> With objDSDBTable.Ta bles(0)
>
> objDBRow = .NewRow()
>
> objDBRow(0) = objDataRow("ACH _BENCHMARK_TEXT ")
>
> objDBRow(1) = objDataRow("ACH _GRADE_RANGE")
>
> objDBRow(2) = objDataRow("ACH _STATE_REF_NUM" )
>
> .Rows.Add(objDB Row)
>
> End With
>
> ObjCmdBuilder = New SqlCommandBuild er(objAdapter)
>
> objAdapter.Upda te(objDSDBTable , "tblstandardste st")
>
> Next
>
> objSQLConn.Clos e()
>
> This only gets the one level. I need all levels to be placed in a SQL[/color]
Server[color=blue]
> Database in relational tables. Please help me!!!!!!!!!!!!! !!!!!!!!!
>
> *************** *******SCHEMA** *************** ***********
>
> <?xml version="1.0" encoding="utf-8"?>
>
> 
>
> <XSDDesignerLay out layoutVersion=" 1" viewPortLeft="-10553"
> viewPortTop="33 44">
>
> <ACH_STATE_SUBJ ECT_XmlElement left="-1561" top="450" width="15557"
> height="3863" selected="0" zOrder="1" index="0">
>
> <ACH_BENCHMARK_ XmlElement left="-688" top="5583" width="13811"[/color]
height="2963"[color=blue]
> selected="0" zOrder="2" index="5">
>
> <ACH_HEADERS_Xm lElement left="-9553" top="9816" width="5292" height="2963"
> selected="0" zOrder="5" index="4" />
>
> <ACH_COMPENDIX_ ALIGNMENT_XmlEl ement left="3466" top="9816" width="12065"
> height="2963" selected="0" zOrder="7" index="5">
>
> <ACH_COMPENDIX_ SUBJ_XmlElement left="-2991" top="14049" width="5292"
> height="2963" selected="0" zOrder="9" index="0" />
>
> <ACH_COMPENDIX_ TRIPLET_XmlElem ent left="3571" top="14049" width="5292"
> height="2963" selected="0" zOrder="11" index="1" />
>
> <ACH_COMPENDIX_ VOCAB_XmlElemen t left="13414" top="14049" width="5292"
> height="2963" selected="0" zOrder="13" index="2">
>
> <ACH_COMPENDIX_ VOCAB_LETTER_Xm lElement left="10133" top="18282"[/color]
width="5292"[color=blue]
> height="2963" selected="0" zOrder="15" index="0" />
>
> <ACH_COMPENDIX_ VOCAB_WORD_XmlE lement left="16695" top="18282" width="5292"
> height="2963" selected="0" zOrder="17" index="1" />
>
> </ACH_COMPENDIX_V OCAB_XmlElement >
>
> </ACH_COMPENDIX_A LIGNMENT_XmlEle ment>
>
> </ACH_BENCHMARK_X mlElement>
>
> </ACH_STATE_SUBJE CT_XmlElement>
>
> <NewDataSet_Xml Element left="33445" top="635" width="5292" height="2963"
> selected="0" zOrder="19" index="1">
>
> <ref_x003D_ACH_ STATE_SUBJECT_X mlElement left="33445" top="4868"[/color]
width="5292"[color=blue]
> height="2963" selected="0" zOrder="20" index="0">
>
> <ACH_BENCHMARK_ XmlElement left="33445" top="9101" width="5292"[/color]
height="2963"[color=blue]
> selected="0" zOrder="22" index="5">
>
> <ACH_HEADERS_Xm lElement left="20321" top="13334" width="5292"[/color]
height="2963"[color=blue]
> selected="0" zOrder="24" index="4" />
>
> <ACH_COMPENDIX_ ALIGNMENT_XmlEl ement left="36726" top="13334" width="5292"
> height="2963" selected="0" zOrder="26" index="5">
>
> <ACH_COMPENDIX_ SUBJ_XmlElement left="26883" top="17567" width="5292"
> height="2963" selected="0" zOrder="28" index="0" />
>
> <ACH_COMPENDIX_ TRIPLET_XmlElem ent left="33445" top="17567" width="5292"
> height="2963" selected="0" zOrder="30" index="1" />
>
> <ACH_COMPENDIX_ VOCAB_XmlElemen t left="43288" top="17567" width="5292"
> height="2963" selected="0" zOrder="32" index="2">
>
> <ACH_COMPENDIX_ VOCAB_LETTER_Xm lElement left="40007" top="21800"[/color]
width="5292"[color=blue]
> height="2963" selected="0" zOrder="34" index="0" />
>
> <ACH_COMPENDIX_ VOCAB_WORD_XmlE lement left="46569" top="21800" width="5292"
> height="2963" selected="0" zOrder="36" index="1" />
>
> </ACH_COMPENDIX_V OCAB_XmlElement >
>
> </ACH_COMPENDIX_A LIGNMENT_XmlEle ment>
>
> </ACH_BENCHMARK_X mlElement>
>
> </ref_x003D_ACH_S TATE_SUBJECT_Xm lElement>
>
> </NewDataSet_XmlE lement>
>
> </XSDDesignerLayo ut>
>
> *************** *************** *******END SCHEMA********* ***************
>
>[/color]

importing XML doc into SQL Server

importing XML doc into SQL Server

Comment