Friday, March 30, 2012
Load Data to SQL SERVER
1. OPENXML
2. BULK LOAD
3. UpdateGRAM.
Thanks
This depends a lot on how much data you have to load and how complex the
schema you're loading it into is. Updategrams are not really suited to
loading large quantities of XML. Bulk Load is good for loading a lot of
data and doesn't require a lot of coding but it does require a mapping
schema to map the XML schema to the database schema. OpenXML is pretty easy
to code and doesn't require a mapping schema but it has a tendency to run
out of memory if you try to load very large documents.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MK" <anonymous@.discussions.microsoft.com> wrote in message
news:20c8d01c45a00$53af6540$a301280a@.phx.gbl...
> Which way will be easy to load XML data to SQL Server 2000
> 1. OPENXML
> 2. BULK LOAD
> 3. UpdateGRAM.
> Thanks
Load data in SQL Using SQLXML 3.0
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 data from web page into sp (sql2000)
procedure using just a web address? The data will just be text/xml or
some such.
cheers,
ChrisHello Chris,
Not easily sorry.
Easy in SQL 2005 with a CLR function.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Hi, using sql server 2000, is there a way I can load data into a
> stored procedure using just a web address? The data will just be
> text/xml or some such.
> cheers,
> Chris|||Simon Sabin wrote:
> Not easily sorry.
> Easy in SQL 2005 with a CLR function.
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
Thanks Simon.. I'm currently figuring it out using ado in an activex
script... would probably do it quicker upgrading to 2005 :D
cheers,
Chris
Load data from web page into sp (sql2000)
procedure using just a web address? The data will just be text/xml or
some such.
cheers,
Chris
Hello Chris,
Not easily sorry.
Easy in SQL 2005 with a CLR function.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
> Hi, using sql server 2000, is there a way I can load data into a
> stored procedure using just a web address? The data will just be
> text/xml or some such.
> cheers,
> Chris
|||Simon Sabin wrote:[vbcol=seagreen]
> Not easily sorry.
> Easy in SQL 2005 with a CLR function.
>
> Simon Sabin
> SQL Server MVP
> http://sqlblogcasts.com/blogs/simons
Thanks Simon.. I'm currently figuring it out using ado in an activex
script... would probably do it quicker upgrading to 2005 :D
cheers,
Chris
Monday, March 26, 2012
Load an XML doc from a file
XML document from a file on disk (ex.: c:\temp\mydoc.xml) instead of pasting
it into my code. Is there a way to do this in MSSQL 2005?
Thank you.
DECLARE @.idoc int
declare @.xmlDocument xml
set @.xmlDocument = N'<?xml version="1.0"?>
<gpx version="1.1"
creator="GMapToGPX 4.14 - http://www.elsewhere.org/GMapToGPX/"
xmlns="http://www.topografix.com/GPX/1/1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.topografix.com/GPX/1/1
http://www.topografix.com/GPX/1/1/gpx.xsd">
<rte>
<name>Gmaps Pedometer Route</name>
<cmt>Permalink: <![CDATA[Permalink temporarily unavailable.]]>
</cmt>
<rtept lat="32.45358" lon="-110.97512">
<name>Start</name>
<ele>901.73556</ele>
</rtept>
<rtept lat="32.4537" lon="-110.97549">
<name>Turn 1</name>
<ele>902.01902</ele>
</rtept>
<rtept lat="32.45377" lon="-110.97594">
<name>Turn 2</name>
<ele>901.95196</ele>
</rtept>
<rtept lat="32.45376" lon="-110.97653">
<name>Turn 3</name>
<ele>902.22324</ele>
</rtept>
</rte>
</gpx>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xmlDocument, '<gpx
xmlns:gpxns="http://www.topografix.com/GPX/1/1"/>'
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML (@.idoc, '/gpxns:gpx/gpxns:rte/gpxns:rtept',3)
WITH (lat float, lon float, name varchar(60) 'gpxns:name', ele float
'gpxns:ele')
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
Alain Quesnel wrote:
> declare @.xmlDocument xml
> set @.xmlDocument = N'<?xml version="1.0"?>
One way is like this
SET @.xmlDocument = (
SELECT * FROM OPENROWSET(
BULK 'C:\dir\subdir\subdir\file.xml',
SINGLE_BLOB
) AS x
);
then you can use the variable @.xmlDocument as you have done before with
the stored procedure.
See the documentation here:
<http://msdn2.microsoft.com/en-us/library/ms191184.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
Load an XML doc from a file
XML document from a file on disk (ex.: c:\temp\mydoc.xml) instead of pasting
it into my code. Is there a way to do this in MSSQL 2005?
Thank you.
DECLARE @.idoc int
declare @.xmlDocument xml
set @.xmlDocument = N'<?xml version="1.0"?>
<gpx version="1.1"
creator="GMapToGPX 4.14 - http://www.elsewhere.org/GMapToGPX/"
xmlns="http://www.topografix.com/GPX/1/1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.topografix.com/GPX/1/1
http://www.topografix.com/GPX/1/1/gpx.xsd">
<rte>
<name>Gmaps Pedometer Route</name>
<cmt>Permalink: <![CDATA[Permalink temporarily unavailable.]]>
</cmt>
<rtept lat="32.45358" lon="-110.97512">
<name>Start</name>
<ele>901.73556</ele>
</rtept>
<rtept lat="32.4537" lon="-110.97549">
<name>Turn 1</name>
<ele>902.01902</ele>
</rtept>
<rtept lat="32.45377" lon="-110.97594">
<name>Turn 2</name>
<ele>901.95196</ele>
</rtept>
<rtept lat="32.45376" lon="-110.97653">
<name>Turn 3</name>
<ele>902.22324</ele>
</rtept>
</rte>
</gpx>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.xmlDocument, '<gpx
xmlns:gpxns="http://www.topografix.com/GPX/1/1"/>'
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML (@.idoc, '/gpxns:gpx/gpxns:rte/gpxns:rtept',3)
WITH (lat float, lon float, name varchar(60) 'gpxns:name', ele float
'gpxns:ele')
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comAlain Quesnel wrote:
> declare @.xmlDocument xml
> set @.xmlDocument = N'<?xml version="1.0"?>
One way is like this
SET @.xmlDocument = (
SELECT * FROM OPENROWSET(
BULK 'C:\dir\subdir\subdir\file.xml',
SINGLE_BLOB
) AS x
);
then you can use the variable @.xmlDocument as you have done before with
the stored procedure.
See the documentation here:
<http://msdn2.microsoft.com/en-us/library/ms191184.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||Works like a charm.
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:OLcWpOogHHA.2368@.TK2MSFTNGP04.phx.gbl...
> Alain Quesnel wrote:
>
> One way is like this
> SET @.xmlDocument = (
> SELECT * FROM OPENROWSET(
> BULK 'C:\dir\subdir\subdir\file.xml',
> SINGLE_BLOB
> ) AS x
> );
> then you can use the variable @.xmlDocument as you have done before with
> the stored procedure.
> See the documentation here:
> <http://msdn2.microsoft.com/en-us/library/ms191184.aspx>
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/
Load a XML file to a variable
When I look into the XML Source component, there is an option of XMldata from varialbe, can anybody help me in knowing how exactly do I go about in loading an XML file onto a variable ?
Variety of ways to load xml data from a file into a variable. Here's one:
One is to use the XML task with a no-op xslt. The xsl transform doesn't do anything, so the unmodified file data ends up the variable.
Operation Type: XSLT
SourceType: File connection
SaveOperationResult: True
Destination: SomeStringVariable
DestinationType: Variable
OverwriteDestination: True
SecondOperandType: Direct input
Second Operand:
Code Snippet
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output encoding="utf-16" />
<xsl:template match="*">
<xsl:copy-of select = "." />
</xsl:template>
</xsl:stylesheet>
|||
Thanks a lot for the reply!!!
But this is what am trying to do.. I have a variable onto which am storing the complete path location where my XML file is stored. Say "C:\\Temp\\A.xml" I also have a Stored Proc in my Database that does accept an XML doc as an input parameter. Now is there a way I can pick the XML file load it onto a variable and pass it onto the Stored Proc
So, supposing you had a source xml like the following
<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<book>
<chapter name="all play and no work"></chapter>
<chapter name="all work and no play"></chapter>
</book>
and a stored procedure which counted up the number of chapters
CREATE PROCEDURE dbo.CountOfChapters
(
@.doc xml
)
AS
SET NOCOUNT ON
SELECT @.doc.value('count(//chapter)','int') as CountOfChapters
RETURN
First, create an XML task, using the no-op xslt set to the following xsl transform to get the data into an IS string variable
Code Snippet
<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output encoding="utf-16" />
<xsl:template match="*">
<xsl:copy-of select = "." />
</xsl:template>
</xsl:stylesheet>
Follow that XML task with an Execute SQL task. Use an ADO.NET SqlClient connection manager, and in the Parameter Mapings table, map the sproc's @.doc input parameter to a DataType of Xml and to the String variable loaded by the XSLT task. Set the ResultProperty of the SQL task to "Single Row", mapping into an IS variable of type In32. A count of 2 is loaded into the IS variable.
|||
jaegd wrote:
First, create an XML task, using the no-op xslt set to the following xsl transform to get the data into an IS string variable<?xml version="1.0"?>
<xsltylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xslutput encoding="utf-16" />
<xsl:template match="*">
<xsl:copy-of select = "." />
</xsl:template></xsltylesheet>
Follow that XML task with an Execute SQL task. Use an ADO.NET SqlClient connection manager, and in the Parameter Mapings table, map the sproc's @.doc input parameter to a DataType of Xml and to the String variable loaded by the XSLT task. Set the ResultProperty of the SQL task to "Single Row", mapping into an IS variable of type In32. A count of 2 is loaded into the IS variable.
Sorry to ask u again, but can you please explain a bit here ? I have a XML task, operation type i have given it as XSLT, Source type I have selected as File Connection, given a value for the source field, Selected Destination as the string variable to be passed into the SP. What is this Second operand stuff ?
This is slightly confusing. Sorry if am troubling...
|||The second operand for the XML task, when then operation is XSLT, is the Xml tranform text itself. SecondOperand is an "accurate" name, but it not a helpful one.Paste the text of the xml transform into the SecondOperand property. The problem might be that the forum's "smiley" converter trashes (by default) cut and paste of xml, xml schema, xml transforms.
The xsltylesheet really should read "xsl" colon "stylesheet"
The xslutput, which is an interesting conversion of what was originally pasted, should read "xsl" colon "output".
|||
Try enclosing the code in a code snippet.
Code Snippet
xsl:stylesheet
xsl:output
|||Thanks a lot. I'll use that from now on.|||
jaegd wrote:
The second operand for the XML task, when then operation is XSLT, is the Xml tranform text itself. SecondOperand is an "accurate" name, but it not a helpful one. Paste the text of the xml transform into the SecondOperand property. The problem might be that the forum's "smiley" converter trashes (by default) cut and paste of xml, xml schema, xml transforms.
The xsltylesheet really should read "xsl" colon "stylesheet"
The xslutput, which is an interesting conversion of what was originally pasted, should read "xsl" colon "output".
Thanks a Million it is working now.. But have a slight query over here. you had said about having the Coonection in Souce type in XML Task as File Connection right ? Am afraid it is loading only that particular XML File. How do I make it pick all the XML files in the particaular path ? Is this possible ? So close yet so far ....
|||I did try out this..
I got a varialbe totFileName that has the name of the XMl file and the complete path where it si stored, am getign this variable populated with all the xml file names, thanks to the script/for each component. I modified the source type in the XMl Task to Variable instead of File Connection, clicked on the Expressions builder and for the property souce I came up with the expression "@.[User::TotFileName]" and when I clicked on the evaluate expression, I get the complete path name, "C:\\Documents and Settings\\temp\\S_ 22328_0003.xml".
But when I run the package this is the error I get. "Error: Failed to lock variable "S_17470_0003b652.xml" for read/write access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
Something am missing here ?
|||When processsing multiple files, use a ForEach loop container (with a File Enumerator ) with appropriate file name to variable mappings.
The set of contained tasks is the same (XML Task + Execute SQL task) for this problem space. Again, there's almost always more than one way to do it; this will work.
|||
jaegd wrote:
When processsing multiple files, use a ForEach loop container (with a File Enumerator ) with appropriate file name to variable mappings.
The set of contained tasks is the same (XML Task + Execute SQL task) for this problem space. Again, there's almost always more than one way to do it; this will work.
This is eating me head... Ok I am doing just what you have specified, I have a ForEachLoop task (node list), Inside which I wrote a Script to get the name of the XML files, and then I have one more ForEach Task with file enumerator. Set of contained task is XML+ExecuteSQl. Now my idea is try to create the directory in which my XML's are loaded dynamically using a variable, then pass the name of the XML to the XML task and load the XML into a varialbe and pass it onto the SP. Looks so simple but no luck its still giving the same error, I had posted earlier. Anyhting I am missing here. Thanks for all the help so far...
|||The place to put the dynamic filename expression is on the file connection manager's connectionString property.
Change the source type of the connection back from Variable to File Connection on XML Task. Put the expression @.[User::TotFileName] on the File Connection manager's ConnectionString property.
The reason for that "variable cannot be found" error is quite simple. You have changed the SourceType on the XML Task from File Connection to Variable, and then put an expression on the XML Task's Source property. Translated, that means the task is going to look for a variable named "S_17470_0003b652.xml", which doesn't and shouldn't exist.
The interpretation of the Source property for the XML Task is based on the Source Type (Direct Input, File connection, or Variable) When the SourceType is "Variable", the task's Source property must be the NAME of a variable, which contains xml. Naturally there is no variable with the name "S_17470_0003b652.xml", that's the name of a file. Now, when the SourceType is "File Connection", the Source task property must be the NAME of a file connection manager, which points to a file containing xml.
Friday, March 23, 2012
Little discussion about XML field performance
I'm starting a study about SQL SERVER 2005. In my job, we need store
millions of data in a database. In this data, have a XML file that are store
d
in a XML field.
The question is: How will be database performance? We have this doubts
because will be data a lot! The average is 10 millions of data add for month
in same table.
Somebody have a cases of this subject?
Sorry my english! :o)
Tks!
Luiz
Procergs - Porto Alegre - BrazilNice Monday. It's raining in Seoul.
This article may help.
http://msdn.microsoft.com/library/d...>
h_SQLXML.asp
Save XML in database, but don't query it as far as possible. That's my
private opinion.
Pohwan Han. Seoul. Have a nice day.
"Luiz Carlos" <LuizCarlos@.discussions.microsoft.com> wrote in message
news:602B8FB8-3238-440A-BE4D-FB6DDFB851D3@.microsoft.com...
> Hello!
> I'm starting a study about SQL SERVER 2005. In my job, we need store
> millions of data in a database. In this data, have a XML file that are
> stored
> in a XML field.
> The question is: How will be database performance? We have this doubts
> because will be data a lot! The average is 10 millions of data add for
> month
> in same table.
> Somebody have a cases of this subject?
> Sorry my english! :o)
> Tks!
> Luiz
> Procergs - Porto Alegre - Brazil
Friday, February 24, 2012
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)