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

Load Balancing Clustering

Hi,
Would it be safe to say that SQL server does not allow me to do load
balancing of a single database? We have an application that uses a single
database. Currently I have a setup of 2 decent servers (HP ML570). They are
setup with Windows 2003 server failover clustering. I wanted to find out
whether I can change the cluster to an active/active cluster and use the
same database for load balancing using both my servers a bit more
efficiently.
Thank you.
Clustering is a hardware fail over solution only. It does not in any way
shape or form do load balancing.
Andrew J. Kelly SQL MVP
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:u%23NrpfDjEHA.1712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Would it be safe to say that SQL server does not allow me to do load
> balancing of a single database? We have an application that uses a single
> database. Currently I have a setup of 2 decent servers (HP ML570). They
are
> setup with Windows 2003 server failover clustering. I wanted to find out
> whether I can change the cluster to an active/active cluster and use the
> same database for load balancing using both my servers a bit more
> efficiently.
> Thank you.
>
|||Thank you Andrew for your reply.
Is there any other way to do SQL load balancing for a single database?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ReExkDjEHA.636@.TK2MSFTNGP12.phx.gbl...
> Clustering is a hardware fail over solution only. It does not in any way
> shape or form do load balancing.
> --
> Andrew J. Kelly SQL MVP
>
> "Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
> news:u%23NrpfDjEHA.1712@.TK2MSFTNGP09.phx.gbl...
> are
>
|||Not easily unless the database is read only. There are some 3rd party tools
that claim to help some like http://www.xprime.com/ but I can't vouch for
them as of yet. I haven't actually seen one in action yet. I know it
sounds obvious enough but what is the reason for wanting to do load sharing?
Scaling up is a lot easier and usually cheaper than scaling out.
Andrew J. Kelly SQL MVP
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:e5aO3pDjEHA.3664@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Thank you Andrew for your reply.
> Is there any other way to do SQL load balancing for a single database?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23ReExkDjEHA.636@.TK2MSFTNGP12.phx.gbl...
way[vbcol=seagreen]
single[vbcol=seagreen]
out[vbcol=seagreen]
the
>
|||Just to add to Andrew's note. The key in performance tuning is to first
determine what component is underperforming, then improve that component.
If you are memory bound, add more ram, disk too slow, move to from Raid 5
to Raid 10. TempDB I/O bound, move it to a separate drive, etc.
Some of the SQL Server performance tuning books can help scale-up the SQL
Server hardware subsystems long before you'll probably need to "scale out".
Chris Skorlinski
Microsoft SQL Server Support
Please reply directly to the thread with any updates.
This posting is provided "as is" with no warranties and confers no rights.

Listbox

I have a report with a single parameter, named param1. The parameter is
a list box that accepts multiple values.
When I select a single value from a listbox, the report works fine, But
when I select more than one value, the stored procedure call fails
saying '[Query execution failed for data set 'XXX' Must decalare the
variable '@.param1'.]'
I initially assumed that the multiple values would be passed to my SP
in the form of a single comma-delimited varchar, but this does not
seems to be the case. How can I set up the stored procedure call to
take multiple values from a listbox? Do I need to do something special
in the SP to process the multiple values?Hi,
you will have to write your query like this here:
WHERE SomeColumn IN (@.parametername)
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||It is passed the way you suppose. But, try calling your stored procedure
yourself (not from Reporting Services). Manually pass it a comma separated
string for the parameter. It won't work. This is a stored procedure issue,
not a Reporting Services issue. If you have the query defined in RS you can
do like this: select * from sometable where somefield in (@.MyParam) but you
cannot do this if that statement is in a stored procedure.
What you can do is to have a string parameter that is passed as a multivalue
parameter and then change the string into a table.
This technique was told to me by SQL Server MVP, Erland Sommarskog
For example I have done this
select * from sometable where somefield in (select str from
charlist_to_table(@.MyParam,Default))
So note this is NOT an issue with RS, it is strictly a stored procedure
issue.
Here is the function:
CREATE FUNCTION charlist_to_table
(@.list ntext,
@.delimiter nchar(1) = N',')
RETURNS @.tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @.pos int,
@.textpos int,
@.chunklen smallint,
@.tmpstr nvarchar(4000),
@.leftover nvarchar(4000),
@.tmpval nvarchar(4000)
SET @.textpos = 1
SET @.leftover = ''
WHILE @.textpos <= datalength(@.list) / 2
BEGIN
SET @.chunklen = 4000 - datalength(@.leftover) / 2
SET @.tmpstr = @.leftover + substring(@.list, @.textpos, @.chunklen)
SET @.textpos = @.textpos + @.chunklen
SET @.pos = charindex(@.delimiter, @.tmpstr)
WHILE @.pos > 0
BEGIN
SET @.tmpval = ltrim(rtrim(left(@.tmpstr, @.pos - 1)))
INSERT @.tbl (str, nstr) VALUES(@.tmpval, @.tmpval)
SET @.tmpstr = substring(@.tmpstr, @.pos + 1, len(@.tmpstr))
SET @.pos = charindex(@.delimiter, @.tmpstr)
END
SET @.leftover = @.tmpstr
END
INSERT @.tbl(str, nstr) VALUES (ltrim(rtrim(@.leftover)),
ltrim(rtrim(@.leftover)))
RETURN
END
GO
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"melishbd" <melissa@.hbdc.com> wrote in message
news:1169581326.626144.35060@.v45g2000cwv.googlegroups.com...
>I have a report with a single parameter, named param1. The parameter is
> a list box that accepts multiple values.
> When I select a single value from a listbox, the report works fine, But
> when I select more than one value, the stored procedure call fails
> saying '[Query execution failed for data set 'XXX' Must decalare the
> variable '@.param1'.]'
> I initially assumed that the multiple values would be passed to my SP
> in the form of a single comma-delimited varchar, but this does not
> seems to be the case. How can I set up the stored procedure call to
> take multiple values from a listbox? Do I need to do something special
> in the SP to process the multiple values?
>

list of clustered index in the database

hi guru's

would appreciate if someone could show how to list all the clustered
indexes in the database.

if it can done as a output of single query it would be fine. the output
should be the table name, column name and clustered index name.

thanx
balaI don't think it can be done in a single query, but you can do it like
so:

--declare variables and temp table for accumulation
DECLARE @.tName varchar(200)
CREATE TABLE #t (table_name varchar(200),
index_name varchar(200),
index_description varchar(210),
index_keys nvarchar(2078))

--open cursor for user tables
DECLARE C CURSOR LOCAL FOR
SELECT name
FROM sysobjects
WHERE xtype = 'U'

OPEN C

FETCH NEXT FROM c INTO @.tname

WHILE @.@.FETCH_STATUS = 0
BEGIN

--run sp_helpindex against table in cursor
INSERT INTO #t (index_name, index_description, index_keys)
exec sp_helpindex @.tname

--since sp_helpindex doesn't return a table name,
--have to update the current NULL table_name
UPDATE #t
SET table_name = @.tname
WHERE table_name is NULL

--Loop by getting next row from cursor
FETCH NEXT FROM c INTO @.tname
END

CLOSE C

DEALLOCATE C

--retrieve specified data; limit it to clustered indexes
SELECT table_name, index_name, index_keys
FROM #t
WHERE index_description like 'clustered%'

DROP TABLE #t

HTH,
Stu|||hey stu

thanx for the quick response. will try it out tomorrow in office

regards
bala|||This should work too.

select object_name(id) as table_name, name as index_name
from sysindexes
where indid = 1|||bala (balkiir@.gmail.com) writes:
> would appreciate if someone could show how to list all the clustered
> indexes in the database.
> if it can done as a output of single query it would be fine. the output
> should be the table name, column name and clustered index name.

Here is a query:

SELECT tblname = CASE WHEN ik.keyno = 1 THEN o.name ELSE '' END,
ixname = CASE WHEN ik.keyno = 1 THEN i.name ELSE '' END,
ik.keyno, colname = c.name,
isdesc = CASE indexkey_property(o.id, i.indid, ik.keyno,
'IsDescending')
WHEN 1 THEN 'DESC'
ELSE ''
END
FROM sysobjects o
JOIN sysindexes i ON o.id = i.id
JOIN sysindexkeys ik ON i.id = ik.id
AND i.indid = ik.indid
JOIN syscolumns c ON c.id = ik.id
AND c.colid = ik.colid
WHERE i.indid = 1
ORDER BY o.name, ik.keyno

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ahhhh; I didn't even see the sysindexkeys table. I tried doing it with
INDEX_COL(), but it was a miserable failure.

Stu|||thanx guys. the pointer towards the right direction is much appreciated

bala

List of al lindexes and their properties for a db

sp_helpindex gives information for a single table. Is there a way of getting the same information for all user tables in a database?You may want to use the sys.indexes catalog view.

List Attribute Names for Single Node

Hello,

I am trying to accomplish the following goal:

"For any given node in an xml variable, return via recordset a list of attributes."

Take the following query:

DECLARE @.x xml
SET @.x = '
<Item>
<Data Key="ID" Value="1001" />
<Data Key="Name" Value="Blue" />
<Data Key="Type" Value="Color" />
</Item>'

SELECT x.value('@.Key','varchar(255)') as [Key],
x.value('@.Value','varchar(255)') as [Value]
FROM @.x.nodes('//Item/Data') Data(x)

That will return:

Key | Value
-
ID | 1001
Name | Blue
Type | Color

What I want is to be able to query @.x ahead of time such that I get back something to the effect of:

Attribute

Key
Value

The reason is that I want to be able to build the columns of my SELECT statement dynamically by iterating through the attributes of a node. I would then execute my prepared statement to get that Key/Value recordset back.

Ignoring the structure of my example XML, what I'm returning in my Key/Value recordset, and how I'm going about returning it...all I want is to know whether or not I can get via recordset a list of attributes for an XML node, and if I can, how I do it.

Thanks!
Daniel

This is a bit clumsey, but should work.

WITH AllAttr(Vals)
AS
(SELECT @.x.query('<root>{for $a in /Item/Data/@.* return <attr>{$a}</attr>}</root>'))
SELECT DISTINCT
x.value('local-name(@.*[1])','varchar(255)') as [Attribute]
FROM AllAttr
CROSS APPLY Vals.nodes('/root/attr') Data(x)

|||

For any given node, for example <Data>, you do:

declare @.x xml
SET @.x = '
<Item>
<Data Key="ID" Value="1001" />
<Data Key="Name" Value="Blue" />
<Data Key="Type" Value="Color" />
</Item>'
select distinct x.value('local-name(.)', 'varchar(20)')
from @.x.nodes('//Data/@.*') as ref(x)