Load data in SQL Using SQLXML 3.0

Rather new to xml...
I have played around with the samples for importing XML in SQL server using
SQLXML 3.0
It looks fairly easy when the xml is single level, however, is there a way
to load the following into a table ?
Assume you have invoices from customers in a format as follows...
<Invoices>
<InvoiceID>
<CustomerID></CustomerID>
<InvoiceItems>
<InvoiceLine>
<ItemDescription></ItemDescription>
</InvoiceLine>
</InvoiceItems>
</InvoiceID>
</Invoices>
And you want it placed in a table containing the following columns...
Invoice
CustomerID
InvoiceLine
ItemDescription
...where Invoice and Customer would be repeated for each line. I
understand it is not relational, but I am willing to accept that for now.
Thanks !
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:rgi4d.91720$Np2.30403@.bignews4.bellsouth.net. ..
[snip]
> It looks fairly easy when the xml is single level, however, is there a way
> to load the following into a table ?
The problem is bulk load treats each new element as a new table or row, so
you can't really load that xml directly. However, it is pretty easy to
flatten it. See this FAQ:
http://sqlxml.org/faqs.aspx?faq=24
Bryant
|||Thanks Bryant,
The example that interested me in the SQLXML documentation was done using
VbScript... It dealt with an xsd and xml file. The filetype referenced in
the article below is xsl. How do you "run the XML through the xsl" as the
article states. How do you then reference it in the BulkLoad object model
?
I saw for converting
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:eyULQpNoEHA.3900@.TK2MSFTNGP10.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:rgi4d.91720$Np2.30403@.bignews4.bellsouth.net. ..
> [snip]
> The problem is bulk load treats each new element as a new table or row, so
> you can't really load that xml directly. However, it is pretty easy to
> flatten it. See this FAQ:
> http://sqlxml.org/faqs.aspx?faq=24
> --
> Bryant
>
|||"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:qes4d.92040$Np2.1532@.bignews4.bellsouth.net.. .
> Thanks Bryant,
> The example that interested me in the SQLXML documentation was done using
> VbScript... It dealt with an xsd and xml file. The filetype referenced
> in the article below is xsl. How do you "run the XML through the xsl" as
> the article states. How do you then reference it in the BulkLoad object
> model
What are you using to do the bulk loading? VBScript?
Bryant
|||Yes, I want to create a DTS package, consisting primarily of VbScript, then
run the package as a scheduled job... it will utilize the "SQLXMLBulkLoad"
object model. Since my data has multiple nodes I believe I must first
"flatten" it out. In other words, from the sample I reviewed in the SQLXML
documentation, it appears that the .Execute method is expecting 2
arguments... "TheXsd.xml" and "TheDataItself.xml". I guess I need to know
how to "flatten" the data. The article that you referenced previously
states that you "run it through the xsl". That is the process I am not
understanding (I'm rather new to xml itself). Alternatively, is there a way
to flatten the data using only an xsd ?
Thanks,
Rob
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:OMS3gXSoEHA.2764@.TK2MSFTNGP11.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:qes4d.92040$Np2.1532@.bignews4.bellsouth.net.. .
> What are you using to do the bulk loading? VBScript?
> --
> Bryant
>
|||"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:Rdx4d.93238$Np2.13928@.bignews4.bellsouth.net. ..
[snip]
> I guess I need to know how to "flatten" the data. The article that you
> referenced previously states that you "run it through the xsl". That is
> the process I am not understanding (I'm rather new to xml itself).
You can use MSXML to do this. Here is an example:
http://msdn.microsoft.com/library/en...asp?frame=true

> Alternatively, is there a way to flatten the data using only an xsd ?
No...
Bryant
|||Thanks for the guidance, but I am still doing something wrong...
I've modified the Code to VBScript and get I get the following error
message...
The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then
click the Refresh button, or try again later.

Switch from current encoding to specified encoding not supported. Error
processing resource 'file:///C:/SQLXML/Test.xml'. ...
<?xml version="1.0" encoding="UTF-16"?><ead><eadheader id=""
titleproper="Test" /></ead>
--...Here's the code I am using...
dim xslt
dim xslDoc
dim xslProc
dim xmlDoc
dim myErr
set xslt = CreateObject("Msxml2.XSLTemplate.3.0")
set xslDoc = CreateObject("Msxml2.FreeThreadedDOMDocument.3.0")
xslDoc.async = false
xslDoc.load "C:\SQLXML\EadXsl.xml"
xslt.stylesheet = xslDoc
set xmlDoc = CreateObject("Msxml2.DOMDocument.3.0")
xmlDoc.async = false
xmlDoc.load("C:\SQLXML\EadXml.xml")
set xslProc = xslt.createProcessor()
xslProc.input = xmlDoc
xslProc.transform()
dim filesys, testfile
set filesys = CreateObject("Scripting.FileSystemObject")
set testfile = filesys.CreateTextFile("C:\SQLXML\Test.xml",True)
testfile.Write xslProc.output
testfile.close
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:OHEApnnoEHA.2912@.TK2MSFTNGP10.phx.gbl...
> "Rob C" <rwc1960@.bellsouth.net> wrote in message
> news:Rdx4d.93238$Np2.13928@.bignews4.bellsouth.net. ..
> [snip]
> You can use MSXML to do this. Here is an example:
> http://msdn.microsoft.com/library/en...asp?frame=true
>
> No...
> --
> Bryant
>
|||Please disregard,,, I figured it out...
1. There is a small syntax error in the example posted at
http://sqlxml.org/faqs.aspx?faq=24
The xsl should read <xsl:value-of select="eadid"/> NOT
<xsl:value-of select="eaid"/> (missing "d" in "eaid")
2. There is a later version of the template and model I should be using in
tee VB Script (5 as opposed to 3)
Thanks,
Rob
"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:6Kf5d.104016$Np2.19198@.bignews4.bellsouth.net ...
> Thanks for the guidance, but I am still doing something wrong...
> I've modified the Code to VBScript and get I get the following error
> message...
> The XML page cannot be displayed
> Cannot view XML input using style sheet. Please correct the error and then
> click the Refresh button, or try again later.
>
> ----
> Switch from current encoding to specified encoding not supported. Error
> processing resource 'file:///C:/SQLXML/Test.xml'. ...
> <?xml version="1.0" encoding="UTF-16"?><ead><eadheader id=""
> titleproper="Test" /></ead>
> --...Here's the code I am using...
> dim xslt
> dim xslDoc
> dim xslProc
> dim xmlDoc
> dim myErr
> set xslt = CreateObject("Msxml2.XSLTemplate.3.0")
> set xslDoc = CreateObject("Msxml2.FreeThreadedDOMDocument.3.0")
> xslDoc.async = false
> xslDoc.load "C:\SQLXML\EadXsl.xml"
> xslt.stylesheet = xslDoc
> set xmlDoc = CreateObject("Msxml2.DOMDocument.3.0")
> xmlDoc.async = false
> xmlDoc.load("C:\SQLXML\EadXml.xml")
> set xslProc = xslt.createProcessor()
> xslProc.input = xmlDoc
> xslProc.transform()
> dim filesys, testfile
> set filesys = CreateObject("Scripting.FileSystemObject")
> set testfile = filesys.CreateTextFile("C:\SQLXML\Test.xml",True)
> testfile.Write xslProc.output
> testfile.close
>
> "Bryant Likes" <bryant@.suespammers.org> wrote in message
> news:OHEApnnoEHA.2912@.TK2MSFTNGP10.phx.gbl...
>
|||"Rob C" <rwc1960@.bellsouth.net> wrote in message
news:Gox5d.109249$Np2.11641@.bignews4.bellsouth.net ...
> Please disregard,,, I figured it out...
> 1. There is a small syntax error in the example posted at
> http://sqlxml.org/faqs.aspx?faq=24
> The xsl should read <xsl:value-of select="eadid"/> NOT <xsl:value-of
> select="eaid"/> (missing "d" in "eaid")
I'll have to fix that... Thanks!
Bryant
sql