InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 12:16:57 GMT20http://www.sqlservercentral.com/Forums/Topic1306043-21-1.aspxHi allI want to add something to a stored procedure plan/stats query I am working on. I want to list the parameters which were used to compile each statement in a stored procedure.So i am running the below query to examine the XML in the query plan. Where a stored proc i am examining has more than one statement, i only want the parameters for that statement.The only way i can see of doing this is to join the sys.dm_exec_query_stats column "query_plan_hash" to the shredded equivalent in the query plan: there is an attribute called @QueryPlanHash (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/ParameterList/ColumnReference)I was quite pleased with this approach! But the join doesn't work because i cannot cast the query plan XML's representation of @QueryPlanHash to binary(8), which is the data type of sys.dm_exec_query_stats column.query_plan_hash. This returns a NULL value from the SQLXML Value function. And I can't work out if i can do anything with the varchar value that i am able to get back from Value, in terms of casting that to a binary(8) on the TSQL side. That doesn't seem to work.Needless to say if i have no join at all, i get a cartesian product - each statement is repeated N times, where N is the number of statements in the query plan. Each statement's parameter list is repeated for each statement.Please see query below, i hope this will explain better what i am trying to do.If anyone knows how i can cast @QueryPlanHash from the XML in such a way that it can join to sys.dm_exec_query_stats column.query_plan_hash, i'd be grateful to know it. Whether that's a CAST in the XML query or a CAST on the TSQL side, either way that would be great!Or perhaps someone has found a more appropriate way to tie an individual sql statement's parameters (or other part of ITS own portion of the overall plan for the batch or SP) to the query's row in sys.dm_exec_query_stats? If so, again i'd be very grateful to hear about it.Many thanks for reading and any help you are able to provide.James[code="sql"]WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT DB_NAME(qp.dbid) as 'database_name', REPLACE(SUBSTRING(st.text, PATINDEX('%CREATE %PROCEDURE%', st.text), 100), CHAR(13) + CHAR(10), ' ') 'Stored Procedure Name', REPLACE( SUBSTRING( st.text , (qs.statement_start_offset/2) + 1 , ((case qs.statement_end_offset when -1 then datalength(text) else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1 ) , CHAR(13) + CHAR(10) , ' ') as 'Statement_text', qs.creation_time, qs.last_execution_time, qs.execution_count-- THESE ARE THE 2 COLUMNS I AM TRYING TO JOIN, N.c.value('@QueryPlanHash', 'varchar(128)') 'QueryPlanHash' , qs.query_plan_hash-- THIS IS WHAT THIS PROBLEM IS ALL ABOUT - GETTING A SPECIFIC PARAMETER OR A LIST OF THEM (IN XML), N.c.value('QueryPlan[1]/ParameterList[1]/ColumnReference[attribute::Column="@ip_FirmCode"][1]/@ParameterCompiledValue', 'varchar(128)') 'Firm_code_compiled_for', N.c.query('QueryPlan/ParameterList/ColumnReference') 'All_params', qs.total_worker_time/1000 AS [Total CPU Time milliseconds], qs.total_worker_time/execution_count/1000 AS [Avg CPU Time milliseconds], qs.total_elapsed_time/1000 [Total duration milliseconds], qs.total_elapsed_time/execution_count/1000 [Avg duration milliseconds], qs.plan_handle, qp.query_planFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stCROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qpOUTER APPLY qp.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') as N(c) WHERE st.text like '%create%procedure%_jm_p_DBA_QueryPlanParamsTest_s%' and not (st.text like '%sys.dm_exec_query_stats%' or st.text like '%dm_exec_cached_plans%' or st.text like '%s_OptimiseExecutionPlans_u%')-- HERE IS THE ATTEMPTED JOIN, NONE OF THE 3 ATTEMPTED JOINS WORK (NO ROWS RETURNED) AND NO JOIN MEANS CARTESIAN PRODUCT, I.E. I DON'T GET THE PARAMS JUST FOR THE -- INDIVIDUAL STATEMENT --and qs.query_plan_hash = N.c.value('@QueryPlanHash', 'VARCHAR(128)') --and qs.query_plan_hash = N.c.value('@QueryPlanHash', 'BINARY(8)') and qs.query_plan_hash = CAST(N.c.value('@QueryPlanHash', 'varchar(128)') AS BINARY(8))[/code]Thu, 24 May 2012 12:05:08 GMTjmanlyhttp://www.sqlservercentral.com/Forums/Topic1302211-21-1.aspxHi all , I'm a SQL Guy but i was asked to look into this following java script .js file and xslt code. Currently i have performance issue with my xslt code.Let me explain you everthing in detail. I have a .js file when i double click .js file it will take the .csv in that folder and uses the xslt and gives me an .xml output file. For better understanding of the requirement please do the following../* code for my .js file */ (Please copy this to note pad and save it as InvoiceAdjustment.js) [code="plain"]showAbsolutePath("InvoiceAdjustment.js");function showAbsolutePath(path){ var fso = new ActiveXObject("Scripting.FileSystemObject"); var s = ""; s += fso.GetAbsolutePathName(path); var foldername = s.substring(0,s.indexOf(path,0)); showFolderFileList(foldername);}function showFolderFileList(foldername){ var fso, f, fc, filename; fso = new ActiveXObject("Scripting.FileSystemObject"); f = fso.GetFolder(foldername); fc = new Enumerator(f.files); for (; !fc.atEnd(); fc.moveNext()) { filename = ""; filename += fc.item(); if (filename.slice(-4) == ".csv") { convertCSVtoXML(foldername, filename); } } fso = null;}function convertCSVtoXML(foldername, filename){ var objDOMDocument = new ActiveXObject("MSXML2.DOMdocument.4.0"); objDOMdocument.async = false; //Create Header objDOMdocument.appendChild(XMLHeader(objDOMDocument)); var objXMLDOMNode = objDOMdocument.documentElement.selectSingleNode("//Document"); // Declare XML object -- this makes it easier to pass as a parameter var objXML = new Object(); // Open the extracted csv from zip file var fso = new ActiveXObject("Scripting.FileSystemObject"); var csvFilename = filename; var tso = fso.OpenTextFile(csvFilename, 1); var strInput; // Loop through the file while(!tso.AtEndOfStream) { strInput = tso.ReadLine(); var vInputLine = strInput.split(","); objXML.a = vInputLine[0]; objXML.b = vInputLine[1]; objXML.c = vInputLine[2]; objXML.d = vInputLine[3]; objXML.e = vInputLine[4]; if (objXML.a != 'RebateInvoiceID') { objXMLDOMNode.appendChild(XMLFileNode(objDOMDocument,objXML)); } } tso.Close(); // Load Transform file var TransformXSL = new ActiveXObject("MSXML2.DOMdocument.4.0"); TransformXSL.async = false TransformXSL.load(foldername + "\\InvAdj.xsl"); // Load XML file and transform it var TempStagingDoc = new ActiveXObject("MSXML2.DOMdocument.4.0"); TempStagingDoc.async = false; TempStagingDoc.loadXML(objDOMdocument.xml); var FinalStr = TempStagingDoc.transformNode(TransformXSL); // Grab just the file name minus any extension var fn = filename.substring(0, filename.indexOf(".csv")); tmpxml = fn + ".xml"; // Write out the transformed file. If writing out just the xml file before transform // FSObject.WriteLine(objDOMdocument.xml); var FSObject = fso.CreateTextFile(tmpxml, true); FSObject.WriteLine(FinalStr); FSObject.Close(); /* // Create empty .flag file then rename it tmpxml += ".flag"; var fsoEmptyFile = fso.CreateTextFile(tmpxml, true); fsoEmptyFile.Close(); // Rename the .flag file to .done var donefilename = tmpxml.substring(0,tmpxml.indexOf(".flag",0)); donefilename += ".done"; fso.MoveFile(tmpxml, donefilename); // Delete the corresponding .csv file fso.DeleteFile(csvFilename); */ // Clear all objects objDOMDocument = null; fso = null; TransformXSL = null; objXML = null; TempStagingDoc = null;}function XMLHeader(objDOMDocument){ var XMLHead; XMLHead = objDOMdocument.createNode(1, "Document",""); var objXMLDOMAttribute = objDOMdocument.createAttribute("Version"); objXMLDOMAttribute.text = "1.0"; XMLHead.attributes.setNamedItem(objXMLDOMAttribute); var objXMLDOMAttribute = objDOMdocument.createAttribute("CreationTimestamp"); objXMLDOMAttribute.text = getTimestamp(); XMLHead.attributes.setNamedItem(objXMLDOMAttribute); return(XMLHead);} function XMLFileNode(objDOMDocument,objXML){ var objXMLDOMNode = objDOMdocument.createNode(1, "RawXMLRow",""); objXMLDOMElement = objDOMdocument.createElement("RebateInvoiceID"); objXMLDOMElement.text = objXML.a; objXMLDOMNode.appendChild(objXMLDOMElement); objXMLDOMElement = objDOMdocument.createElement("RebateEventID"); objXMLDOMElement.text = objXML.b; objXMLDOMNode.appendChild(objXMLDOMElement); objXMLDOMElement = objDOMdocument.createElement("BusinessUnitCode"); function getBusinessUnitCode(){ var r = String(objXML.c).length; if(r == 4) return String(objXML.c); if( r == 3) return "0"+String(objXML.c); if(r == 2){ return "00"+String(objXML.c); } else return "000"+String(objXML.c); } objXMLDOMElement.text = getBusinessUnitCode(); objXMLDOMNode.appendChild(objXMLDOMElement); objXMLDOMElement = objDOMdocument.createElement("CategoryID"); objXMLDOMElement.text = objXML.d; objXMLDOMNode.appendChild(objXMLDOMElement); objXMLDOMElement = objDOMdocument.createElement("PaymentAmount"); objXMLDOMElement.text = objXML.e; objXMLDOMNode.appendChild(objXMLDOMElement); return(objXMLDOMNode);}function getTimestamp(){ var d = new Date(); var mm = padZeros(d.getMonth()+1); var dd = padZeros(d.getDate()); var hh = padZeros(d.getHours()); var mn = padZeros(d.getMinutes()); var ss = padZeros(d.getSeconds()); return d.getFullYear()+"-"+ mm +"-"+ dd +"T"+ hh +":"+ mm +":"+ ss;}function padZeros(s){ if (s < 10) { s = "0" + s; } return(s);}function getDate(stringDate){ var a = String(stringDate).split( "/" ); var strXML ; strDate = "" ; for ( var i = 0 ; i < a.length; i++ ) { if (a[i].length == 1) strDate += "0" + a[i]; else strDate += a[i]; } strDate += "" ; return strDate;}[/code]/* code for xslt */ ( make an xslt using below code and save it as InvAdj.xsl)[code="xml"]<?xml version="1.0"?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:script="script" exclude-result-prefixes="msxsl"> <xsl:output omit-xml-declaration="yes"/> <!-- ROOT --> <xsl:template match="/"> <EnterpriseDocument> <xsl:attribute name="InterfaceName">RebateInvoiceAdjustmentImport</xsl:attribute> <xsl:attribute name="ClientID"> <xsl:choose> <xsl:when test="normalize-space(ClientID)"> <xsl:value-of select="//Document/RawXMLRow/ClientID"/> </xsl:when> <xsl:otherwise>1000001</xsl:otherwise> </xsl:choose> </xsl:attribute> <xsl:attribute name="ClientName">XXXX</xsl:attribute> <xsl:attribute name="Version">1.0</xsl:attribute> <xsl:attribute name="CreationSource">File</xsl:attribute> <xsl:attribute name="CreationTimestamp"> <xsl:value-of select="script:getTimestamp()"/> </xsl:attribute> <xsl:call-template name="InvAdjData"/> </EnterpriseDocument> </xsl:template> <xsl:template name="InvAdjData"> <xsl:for-each select="//Document/RawXMLRow[not(RebateInvoiceID=preceding-sibling::RawXMLRow/RebateInvoiceID)]"> <xsl:sort select="RebateInvoiceID"/> <RebateInvoice> <xsl:attribute name="RebateInvoiceID"> <xsl:value-of select="RebateInvoiceID"/> </xsl:attribute> <xsl:variable name="InvoiceID"> <xsl:value-of select="RebateInvoiceID"/> </xsl:variable> <xsl:for-each select="//Document/RawXMLRow[not(RebateEventID=preceding-sibling::RawXMLRow[RebateInvoiceID=$InvoiceID]/RebateEventID)]"> <xsl:if test="RebateInvoiceID=$InvoiceID"> <xsl:variable name="EventID"> <xsl:value-of select="RebateEventID"/> </xsl:variable> <RebateProgram> <xsl:attribute name="RebateEventID"> <xsl:value-of select="RebateEventID"/> </xsl:attribute> <xsl:for-each select="//Document/RawXMLRow[not(BusinessUnitCode=preceding-sibling::RawXMLRow[RebateInvoiceID=$InvoiceID and RebateEventID=$EventID]/BusinessUnitCode)]"> <xsl:if test="RebateInvoiceID=$InvoiceID and RebateEventID=$EventID"> <xsl:variable name="BUCode"> <xsl:value-of select="BusinessUnitCode" /> </xsl:variable> <BusinessUnit> <xsl:attribute name="BusinessUnitCode"> <xsl:value-of select="BusinessUnitCode"/> </xsl:attribute> <xsl:for-each select="//Document/RawXMLRow[not(CategoryID=preceding-sibling::RawXMLRow[RebateInvoiceID=$InvoiceID and RebateEventID=$EventID and BusinessUnitCode=$BUCode]/CategoryID)]"> <xsl:if test="RebateInvoiceID=$InvoiceID and RebateEventID=$EventID and BusinessUnitCode=$BUCode"> <ItemCategoryPayment> <xsl:attribute name="CategoryID"> <xsl:value-of select="CategoryID"/> </xsl:attribute> <xsl:attribute name="PaymentAmount"> <xsl:value-of select="PaymentAmount"/> </xsl:attribute> </ItemCategoryPayment> </xsl:if> </xsl:for-each> </BusinessUnit > </xsl:if> </xsl:for-each> </RebateProgram > </xsl:if> </xsl:for-each> </RebateInvoice > </xsl:for-each> </xsl:template> <!-- BEGIN JSCRIPT HELPER CODE, KEEP THIS SECTION AS SMALL AS POSSIBLE IT'S A PERFORMANCE HOG --> <msxsl:script language="JScript" implements-prefix="script"> <![CDATA[function getTimestamp(){ var d = new Date(); var m, dy, hr, mn, se; if (d.getMonth()+1 < 10) { m = "0"+(d.getMonth()+1); } else if (d.getMonth()+1 > 9) { m = d.getMonth()+1; } if (d.getDate() < 10) { dy = "0"+(d.getDate()); } else if (d.getDate() > 9) { dy = d.getDate(); } if (d.getHours() < 10) { hr = "0"+(d.getHours()); } else if (d.getHours() > 9) { hr = d.getHours(); } if (d.getMinutes() < 10) { mn = "0"+(d.getMinutes()); } else if (d.getMinutes() > 9) { mn = d.getMinutes(); } if (d.getSeconds() < 10) { se = "0"+(d.getSeconds()); } else if (d.getSeconds() > 9) { se = d.getSeconds(); } return d.getFullYear()+"-"+ m +"-"+ dy +"T"+ hr +":"+ mn +":"+ se;}]]> </msxsl:script></xsl:stylesheet>[/code]Now copy this records into an excel sheet and save it as .csv (SAMPLE INPUT)[code="plain"]RebateInvoiceID RebateEventID BusinessUnitCode CategoryID PaymentAmount1001878 1003042 402 1000057 891001878 1003042 405 1000057 94.421001878 1003043 406 1000057 147.51001878 1003048 423 1000057 97.361001879 1008042 433 1000057 891001879 1008042 434 1000057 70.241001879 1008942 435 1000057 53.081001978 2003042 437 1000057 127.851001978 2003042 438 1000057 180.041001978 3003042 446 1000057 146.611001978 3003042 448 1000057 76.56[/code]Desired output:[code="xml"]- <EnterpriseDocument InterfaceName="RebateInvoiceAdjustmentImport" ClientID="1000001" ClientName="XXXX" Version="1.0" CreationSource="File" CreationTimestamp="2012-05-14T15:33:19" xmlns:script="script">- <RebateInvoice RebateInvoiceID="1001878">- <RebateProgram RebateEventID="1003042">- <BusinessUnit BusinessUnitCode="0402"> <ItemCategoryPayment CategoryID="1000057" PaymentAmount="89" /> </BusinessUnit>- <BusinessUnit BusinessUnitCode="0405"> <ItemCategoryPayment CategoryID="1000057" PaymentAmount="94.42" /> </BusinessUnit> </RebateProgram>- <RebateProgram RebateEventID="1003043">- <BusinessUnit BusinessUnitCode="0406"> <ItemCategoryPayment CategoryID="1000057" PaymentAmount="147.5" /> </BusinessUnit> </RebateProgram>- <RebateProgram RebateEventID="1003048">- <BusinessUnit BusinessUnitCode="0423"> <ItemCategoryPayment CategoryID="1000057" PaymentAmount="97.36" /> </BusinessUnit> </RebateProgram> </RebateInvoice>- <RebateInvoice RebateInvoiceID="1001879">- <RebateProgram RebateEventID="1008042">- <BusinessUnit BusinessUnitCode="0433"> <ItemCategoryPayment CategoryID="1000057" PaymentAmount="89" /> </BusinessUnit>- <BusinessUnit BusinessUnitCode="0434"> <ItemCategoryPayment CategoryID="1000057" PaymentAmount="70.24" /> </BusinessUnit> </RebateProgram>- <RebateProgram RebateEventID="1008942">- <BusinessUnit BusinessUnitCode="0435"> <ItemCategoryPayment CategoryID="1000057" PaymentAmount="53.08" /> </BusinessUnit> </RebateProgram> </RebateInvoice>- <RebateInvoice RebateInvoiceID="1001978">- <RebateProgram RebateEventID="2003042">- <BusinessUnit BusinessUnitCode="0437"> <ItemCategoryPayment CategoryID="1000057" PaymentAmount="127.85" /> </BusinessUnit>- <BusinessUnit BusinessUnitCode="0438"> <ItemCategoryPayment CategoryID="1000057" PaymentAmount="180.04" /> </BusinessUnit> </RebateProgram>- <RebateProgram RebateEventID="3003042">- <BusinessUnit BusinessUnitCode="0446"> <ItemCategoryPayment CategoryID="1000057" PaymentAmount="146.61" /> </BusinessUnit>- <BusinessUnit BusinessUnitCode="0448"> <ItemCategoryPayment CategoryID="1000057" PaymentAmount="76.56" /> </BusinessUnit> </RebateProgram> </RebateInvoice> </EnterpriseDocument>[/code]Now place the .csv input file and .js file and xslt in to one folder and double click the .js file it will generate an .xml file. now i need the same result using muenchain grouping method in xslt code. Can you help me please. Thanks.Thu, 17 May 2012 17:26:29 GMTsam-1083699http://www.sqlservercentral.com/Forums/Topic1293656-21-1.aspxI have a table as below.CREATE TABLE myResultTable( RowId IDENTITY(10000,1) NOT NULL, ItemId INT, ItemResult [text] NULL, PRIMARY KEY(Id))ItemResult column will have XML data. XML Schema will be based on ItemId. That means, for each ItemId, ItemResult will follow specific XMLSchema. For eg, the table may contain rows as below.--------------------------------------------------RowId ItemId ItemResult---------------------------------------------------1 1 <Book Id='1'><Name>BookName</Name></Books>2 2 <Author Id='1'><Name>AuthorName</Name></Author> 3 1 <Book Id='2'><Name>BookName1</Name></Book>4 2 <Author Id='2'><Name>AuthorName1</Name></Author> ----------------------------------------------------There can be n number of ItemIds, that i dont know during real time. Now my requirement is to write a generic function or stored procedure, which should get ItemId as input and converts all corresponding ItemResults into a table. Given ItemId as 2, I should see table representing all Author information. How would I achieve this?Wed, 02 May 2012 02:01:42 GMTn.dineshbabuhttp://www.sqlservercentral.com/Forums/Topic1292154-21-1.aspxI've tried watching videos, reading reference books, reading blogs, and searching this forum, and I still don't understand how to do what I need to do. Here is my XML document:[center][code="xml"]<?xml version="1.0" encoding="utf-8"?><MyRootElement><text>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Curabitur egestas suscipit lectus, et hendrerit nulla malesuada in. Aliquam ultricies felis.</text></MyRootElement>[/code][/center]I have over 31,000 <text> elements in the document inside the <MyRootElement>.I managed to bulk insert the XML file into SQL Server 2012 Express into a table called XmlImportTest. If I do a SELECT * on the table I get two columns in my result set:A column with the filename of my document, and a second column of type xml that seems to contain my entire document. If I click on the second column I get an error that there was an unexpected end of file (which makes me suspicious that the entire file did not load) and I cannot open the file in the XML Editor in SSMS.That aside, what I need to do is insert the values inside the <text> tags into an existing column on another database table. The examples I have seen so far don't match the simplicity of my document (just containing a root element with 31,000+ <text> elements).I need to understand how to do this, or if it's even possible to do inside SQL Server with my document in it's existing state. I get the feeling that since the entire document is in one column in the XmlImportTest table I cannot parse it and separate out the individual <text> element values into multiple rows in the same column in another table. I hope I'm wrong, but that is how it looks to me for now.I tried using the nodes() method in XQuery but could not get any results. I'm stuck and don't know what I'm doing and so far haven't found anything that I can understand to help me.Thanks.Sun, 29 Apr 2012 08:41:07 GMTRHJhttp://www.sqlservercentral.com/Forums/Topic1287900-21-1.aspxHi friends,why we use almost xml scripts in most of the times in MSBI environment, can anybody reason for it?thanks NIhaSun, 22 Apr 2012 23:24:55 GMTniha.736http://www.sqlservercentral.com/Forums/Topic1278501-21-1.aspxHi,I'm sorry if this has been addressed already. I haven't been able to find it.Please consider the following XML.I need help to get to the next level.Each Purchase Order record can relate to 0..n Workorder records which can contain 0..n items.I've shown my 3rd table query but it generates duplicates...ThanksWalterDECLARE @xml XML = '<PurchaseOrder> <PurchaseOrderNumber>1</PurchaseOrderNumber> <WorkOrder> <WorkOrderNumber>11</WorkOrderNumber> <Item> <ItemNumber>111</ItemNumber> </Item> <Item> <ItemNumber>112</ItemNumber> </Item> </WorkOrder> <WorkOrder> <WorkOrderNumber>12</WorkOrderNumber> <Item> <ItemNumber>121</ItemNumber> </Item> <Item> <ItemNumber>122</ItemNumber> </Item> </WorkOrder></PurchaseOrder>'SELECT P.value('PurchaseOrderNumber[1]', 'VARCHAR(20)') PurchaseOrderNumberFROM @xml.nodes('/PurchaseOrder') AS PurchaseOrder(P)SELECT W.value('WorkOrderNumber[1]', 'VARCHAR(20)') WorkOrderNumber, P.value('PurchaseOrderNumber[1]', 'VARCHAR(20)') PurchaseOrderNumber FROM @xml.nodes('/PurchaseOrder/WorkOrder') AS WorkOrder(W)CROSS APPLY @xml.nodes('/PurchaseOrder') AS PurchaseOrder(P) -- this doesn't work: All the items have 2 records (1 for each workorder)SELECT I.value('ItemNumber[1]', 'VARCHAR(20)') ItemNumber, W.value('WorkOrderNumber[1]', 'VARCHAR(20)') WorkOrderNumber, P.value('PurchaseOrderNumber[1]', 'VARCHAR(20)') PurchaseOrderNumber FROM @xml.nodes('/PurchaseOrder/WorkOrder/Item') AS Item(I)CROSS APPLY @xml.nodes('/PurchaseOrder/WorkOrder') AS WorkOrder(W)CROSS APPLY @xml.nodes('/PurchaseOrder') AS PurchaseOrder(P)Wed, 04 Apr 2012 19:01:53 GMTwdjonghttp://www.sqlservercentral.com/Forums/Topic1285724-21-1.aspxPlease refer to the attachment regarding an XML doc I am trying to shred.Questions:1. This XML doc actually occurs in a column. Can I exclude the root element when querying the column?2. How can I exclude specific duplicates of the Data element?Wed, 18 Apr 2012 09:22:43 GMTkennethigirihttp://www.sqlservercentral.com/Forums/Topic1058647-21-1.aspxI'm attempting to use Microsoft XML BulkLoad for SQL Server (SQLXMLBULKLOADLib) to load a six million row file to a [b]single [/b]table.The process aborts with The transaction log for database [] is full.This is running from a .NET application and I'm using the following parameters:[code="other"]Dim connectionString As String = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & AppMgr.DBuser & _ ";Password=" & AppMgr.DBPassword & _ ";Initial Catalog=" & AppMgr.DBName & _ ";Data Source=" & AppMgr.DBServerDim errorFile As String = AppMgr.clsConfig.FilePath & "error.xml"xmlBulkLoad.ConnectionString = connectionStringxmlBulkLoad.ErrorLogFile = errorFilexmlBulkLoad.Transaction = FalsexmlBulkLoad.ForceTableLock =FalsexmlBulkLoad.CheckConstraints =FalsexmlBulkLoad.BulkLoad =TruexmlBulkLoad.Execute(AppMgr.AppDirectory & "E4.xsd", gsDestinationPath & InputConvertedTo)[/code]The process aborts and the following is logged in the error.xml file: [u]"The transaction log for database 'xxxxxx' is full"[/u]The code tests fine for smaller 100,000 row files but I get a few of these huge files.Shouldn't this be a mimimally logged operation?What am I doing wrong? Any help is greatly appreciated.SidPS. I'm using Microsoft XML BulkLoad for SQL Server 4.0 Type Libraryand the Database is in "Simple" recovery mode.Fri, 04 Feb 2011 05:30:01 GMTjcasementhttp://www.sqlservercentral.com/Forums/Topic517940-21-1.aspxDECLARE @UserXML XMLSET @UserXML = ' 'SELECT tblXML.Institution.query('Users/User/Email').value('.','varchar(50)') As Email, tblXML.Institution.query('Users/User/BusinessRoleGuid').value('.','uniqueidentifier') As BusinessRoleGuidFROM @UserXML.nodes('//Institution') tblXML (Institution) How can I get the data is this formatInstitutionGUID Email BusinessRoleGUID0657F7ED-58CA-4A3D-8393-B9A3DD315319 A@Test.com 0657F7ED-58CA-4A3D-8393-B9A3DD315310657F7ED-58CA-4A3D-8393-B9A3DD315319 b@Test.com 1657F7ED-58CA-4A3D-8393-B9A3DD315319Mon, 16 Jun 2008 16:38:28 GMTAmit Lohiahttp://www.sqlservercentral.com/Forums/Topic1278749-21-1.aspxHi I have the following table:[code="sql"]CREATE TABLE XmlTable ([ImportData] [xml] NOT NULL) ON [Primary][/code]I have the following XML format:[code="xml"]<Parent> <ID>1234</ID> <Child> <State>1</State> <Value>X</Value> </Child> <Child> <State>2</State> <Value>Y</Value> </Child></Parent>[/code]and I want to select all the child values like this:[code="sql"]SELECT child.value('(parent::node()/ID/text)[1]', 'NVARCHAR(50)') AS [ID] , child.value('(State/text)[1]', 'NVARCHAR(50)') AS [State] , child.value('(Value/text)[1]', 'NVARCHAR(50)') AS [Value] FROM [XmlTable] AS T CROSS APPLY [ImportData].nodes('/Parent/Child') AS ImportData(child)[/code]I need to restrict (like a WHERE clause) on the value of the State element of the Child element e.g. all States where it isn't 1:[code="other"]ID State Value1234 2 Y[/code]I have tried various methods for half a day now. Can anyone help? Please?PS Please excuse any typos as I am working in a Hyper-V VM with wireless network access so it has all been retyped by hand as I cannot access network from VM :-(Thu, 05 Apr 2012 06:47:49 GMTGary Vargahttp://www.sqlservercentral.com/Forums/Topic1267029-21-1.aspxThis issue will be 5 years old two weeks from tomorrow so we can take out time. But maybe a little poke might be appropriate.I do have to get this solved but I'm thinkin' maybe Pascal, C or our old company DIBOL?We generated XML with that and it looked just fine.The attachment started out as an appendix to my documentation for the project, hence 'Appendix B', but evolved somewhat.The attachment has test data, code, references and snide remarks.It might even be good as a final exam question.You have my permission.ThanksWed, 14 Mar 2012 12:46:44 GMTBig Bay Boaterhttp://www.sqlservercentral.com/Forums/Topic1264203-21-1.aspxI am using sql server 2000 and trying to insert bulk records into a table using XML format. Below is the sample XML format I'm using.<?xml version="1.0" encoding="iso-8859-1"?><batch><data><cardid>010500350000</cardid><pinnumber>•GZ§-ɱÚ</pinnumber></data></batch>And the code to insert it to the table is as follows,DECLARE @BATCHUPLOAD_XML textDECLARE @idoc INTEXEC sp_xml_preparedocument @idoc OUTPUT, @BATCHUPLOAD_XMLINSERT INTO test_table(cardid,pinnumber) SELECT cardid,pinnumberFROM OPENXML (@idoc, '/batch/data') WITH (cardid varchar(100) '@cardid', pinnumber varchar(100) '@pinnumber')This is working fine for normal characters. But if I use an encoded characters like •GZ§-É±Ú or NX”(͵ÒzÉÓ I found that some characters (like • or ”) are replaced by question mark(?) in the database. This is causing lot of issues to me. Any help at the earliest will be greatly appreciated.Thanks,Rakesh.Fri, 09 Mar 2012 03:05:40 GMTrakesh.1263http://www.sqlservercentral.com/Forums/Topic1263462-21-1.aspxHi,I am trying to extract data from an XML file. eg<root><album><producttype>music</producttype><barcode>111111111</barcode><productname>This is a test album</productname><tracks><track><cdno>1</cdno><trackname>my first single</trackname></track><track><cdno>2</cdno><trackname>My second track</trackname></track></tracks><notes><note><text>This album is home produced by myself and hopefully will be liked by many</text></note></notes></album>Is there a way of extracting this in one go or dynamically as i have many XML files that has a samilar structure so my intention is too loop thru xml files with SSIS and process it one at a time based on the info i get perhaps from the XSD file eg.<?xml version="1.0"?><xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <xs:element name="root"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" maxOccurs="unbounded" name="album"> <xs:complexType> <xs:sequence> <xs:choice maxOccurs="unbounded"> <xs:element minOccurs="0" name="producttype" type="xs:string" /> <xs:element minOccurs="0" name="barcode" type="xs:unsignedLong" /> <xs:element minOccurs="0" name="barcodetype" type="xs:string" /> <xs:element minOccurs="0" name="origin" type="xs:string" /> <xs:element minOccurs="0" name="title" type="xs:string" /> <xs:element minOccurs="0" name="artist" type="xs:string" /> <xs:element minOccurs="0" name="altartist" type="xs:string" /> <xs:element minOccurs="0" name="composer" type="xs:string" /> <xs:element minOccurs="0" name="stock" type="xs:string" /> <xs:element minOccurs="0" name="coupling" type="xs:string" /> <xs:element minOccurs="0" name="source" type="xs:string" /> <xs:element minOccurs="0" name="label" type="xs:string" /> <xs:element minOccurs="0" name="media" type="xs:string" /> <xs:element minOccurs="0" name="mediatype" type="xs:string" /> <xs:element minOccurs="0" name="mediacount" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="releasedate" type="xs:string" /> <xs:element minOccurs="0" name="coding" type="xs:string" /> <xs:element minOccurs="0" name="supergenre" type="xs:string" /> <xs:element minOccurs="0" name="features" type="xs:string" /> <xs:element minOccurs="0" name="genre" type="xs:string" /> <xs:element minOccurs="0" name="pricedate" type="xs:date" /> <xs:element minOccurs="0" name="price" type="xs:unsignedInt" /> <xs:element minOccurs="0" name="rrp" type="xs:unsignedInt" /> <xs:element minOccurs="0" name="distribprice" type="xs:unsignedInt" /> <xs:element minOccurs="0" name="import" type="xs:string" /> <xs:element minOccurs="0" name="stocklevel" type="xs:unsignedShort" /> <xs:element minOccurs="0" name="avaliable" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="AudioFormat" type="xs:string" /> <xs:element minOccurs="0" name="runtime" type="xs:unsignedShort" /> <xs:element minOccurs="0" name="agelimit" type="xs:string" /> <xs:element minOccurs="0" name="tracks"> <xs:complexType mixed="true"> <xs:sequence minOccurs="0"> <xs:element minOccurs="0" maxOccurs="unbounded" name="track"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" name="name" type="xs:string" /> <xs:element minOccurs="0" name="cdno" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="trackno" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="artist" type="xs:string" /> <xs:element minOccurs="0" name="asfclip" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> <xs:element minOccurs="0" name="notes"> <xs:complexType mixed="true"> <xs:sequence minOccurs="0"> <xs:element minOccurs="0" name="note"> <xs:complexType mixed="true"> <xs:sequence minOccurs="0"> <xs:element minOccurs="0" name="text" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> <xs:element minOccurs="0" name="pricecode" type="xs:string" /> <xs:element minOccurs="0" name="director" type="xs:string" /> <xs:element minOccurs="0" name="language" type="xs:string" /> <xs:element minOccurs="0" name="VideoFormat" type="xs:string" /> <xs:element minOccurs="0" name="platform" type="xs:string" /> <xs:element minOccurs="0" name="conductor" type="xs:string" /> <xs:element minOccurs="0" name="deleted" type="xs:unsignedByte" /> </xs:choice> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element></xs:schema>Is there also a way of dynamically changing the Output column in the XML Source component of SSIS. If this is possible the is would be simpler to let SSIS do all the work by obtaining an XML file, loop thru the Output columns and import. The move on to the next one.I am not a .net programmer so would appreciate if most of the suggestions could be SSIS or T-SQL.Much appreciated.IsmailThu, 08 Mar 2012 01:24:56 GMTismailmallumhttp://www.sqlservercentral.com/Forums/Topic1261999-21-1.aspxI have inherited a system which is a SQL Database which stores XML data in a Text Column.I have an exe which extracts the data into xml Files and then re-imports using SSIS into a relational format.I have schema files (xsd) but would like to ideally import the data directly into a relational format without the need to export (especially as I need to add more data and the exe is uneditable).I have no real xml experience, so the simpler solution the better.Any thoughts on the best way?Thanks.Tue, 06 Mar 2012 02:14:48 GMTMichaelrohttp://www.sqlservercentral.com/Forums/Topic1256554-21-1.aspxHi[code="sql"]CREATE table #Table1 (ID1 int,ID2 int,ID3 tinyint)DECLARE @x xmlDECLARE @ID1 intSET @ID1 = 1SET @x = '<Products><ProductType> <ProductTypeID>101</ProductTypeID> <Product> <ProductID>1</ProductID> <ProductID>2</ProductID> </Product></ProductType> <ProductType> <ProductTypeID>102</ProductTypeID> <Product> <ProductID>5</ProductID> <ProductID>6</ProductID> </Product></ProductType> </Products>'INSERT INTO [dbo].[#Table1] ( ID1, ID2, ID3 ) SELECT @ID1 ,V.y.value('(./ProductID)[1]','int') AS ID2 ,V.y.value('(//ProductTypeID)[1]','tinyint') AS ID3 --,V.y.value('.','int') AS ProductID FROM @x.nodes('/Products/ProductType/Product') AS V(y) SELECT * FROM #Table1GO DROP TABLE #Table1 GO[/code] I want to insert the value from the xml into table ..pls helpThu, 23 Feb 2012 04:58:23 GMTdilipd006http://www.sqlservercentral.com/Forums/Topic1253495-21-1.aspxHello All - I am newer creating XML and this is a For XML Path. When I do a specific query outside of the xml creation I get the number I am looking for which is the column called SellAmount. So the math would basically be like this.350 * 0.6382SellAmount223.37 but when I run it in the xml it has a messed up conversion. Does anyone have anythoughts on this.[code="xml"]<CostingItem ID="2" Description="Port Charges" UnitQuantity="2" VendorCurrencyCode="GBP" CustomerPaysVendorDirectFlag="false" CustomerPaysVendorLocallyFlag="false" NetAmountChangeReasonCode="ZZZ" CalcMarginAsMarkupNotCommissionFlag="false" CostingType="Base Fare" PERuleAppliedFlag="true" PEProcessedFlag="true" PointsValue="0" PointsCashEquivalentamount="0.00"> <Sell TotalAmount="2.233700000000000e+002" AmountsInVendorCurrencyFlag="false" /> <Net TotalAmount="2.233700000000000e+002" /></CostingItem>[/code] [code="sql"]CASE WHEN SUM(cb.currencyrecnocruiseline) = 1 THEN CONVERT(MONEY,SUM(trans.portcharges)) * (SELECT convrate FROM content..currency_rates WHERE currencyrecno = 2 AND enddate = '2099-01-01 00:00:00.000') ELSE ROUND(RTRIM(SUM(trans.portcharges)),2) END AS "CostingItem/Sell/@TotalAmount",[/code]Thu, 16 Feb 2012 15:15:23 GMTswilliams-1062888http://www.sqlservercentral.com/Forums/Topic1250624-21-1.aspxHey everyone,I searched but can't find any examples describing how to interrogate some specific XML. I have an XML structure that is (shall we say) less than optimal, but for the sake of argument, I can't change it, I have to deal with it as it is. Below is an example of what I'm trying to do===========================================================================DECLARE @hdoc int,@str nvarchar(max)set @str = '<root> <item> <var name="ITEMID"> <number>3520.0</number> </var> <var name="QTY"> <number>2.0</number> </var> <var name="ITEMTYPE"> <string>thingy</string> </var> </item></root>' EXEC sp_xml_preparedocument @hdoc OUTPUT, @str SELECT * FROM OPENXML (@hdoc, '/root/item',8) WITH ( [type] varchar(20) 'var[3]/string', [id] float 'var[1]/number', [qty] float 'var[2]/number' )AS x EXEC sp_xml_removedocument @hdoc===========================================================================I want a recordset with 1 record and 3 columns... Now, this works just fine... However, If the "VAR" columns in the source XML were to be re-ordered this would break.Is there a way to say "make the TYPE column = the STRING of the VAR record who's NAME = "ITEMTYPE"... ?Any ideas?Fri, 10 Feb 2012 16:56:18 GMTtoddkinghamhttp://www.sqlservercentral.com/Forums/Topic1247049-21-1.aspxwhen i install VS 2010i find this error in the attachplz help to complete my install it`s emergencySun, 05 Feb 2012 17:24:03 GMTabeerbalahhttp://www.sqlservercentral.com/Forums/Topic1247947-21-1.aspxHifollwing is the code i had use [code="sql"]DECLARE @GroupID int = 1DECLARE @Agency xmlSET @Agency = '<Agency> <AgencyID>1</AgencyID> <AgencyID>2</AgencyID> <AgencyID>3</AgencyID> </Agency>'CREATE TABLE #temp (id int identity,AgencyID int,GroupID int )INSERT INTO #temp ( AgencyID ,GroupID ) SELECT V.y.value('(//AgencyID)[1]','int') AS AgencyID ,@GroupID FROM @Agency.nodes('/Agency/AgencyID') AS V(y) SELECT * FROM #temp DROP TABLE #temp [/code]this resul is coming while executing the above code, the agency id is not coming right instaed of coming 1,2,3 it is repeating 1id AgencyID GroupID1 1 12 1 13 1 1i want the following resultid AgencyID GroupID1 1 12 2 13 3 1Tue, 07 Feb 2012 03:42:00 GMTdilipd006http://www.sqlservercentral.com/Forums/Topic1246578-21-1.aspxHi,I was wondering how I can accomplished the following:1. Data Field contains an entire XML File2. Extract the contents of that field and load to a new datatableThanks in advanceFri, 03 Feb 2012 08:59:47 GMTJoe Contreras-290946http://www.sqlservercentral.com/Forums/Topic1240594-21-1.aspxHello all.New to XML and any help is appreciated.Code:[code="sql"]IF OBJECT_ID('tempdb..#TableExample') IS NOT NULLDROP TABLE #TableExampleGOCREATE TABLE #TableExample(XMLData xml)INSERT INTO #TableExampleVALUES('<?xml version="1.0" encoding="ISO-8859-1"?><Directory> <Groups GroupName = "Management"> <EmpID>45612</EmpID> <EmpID>84512</EmpID> <EmpID>32247</EmpID> <EmpID>91548</EmpID> <EmpID>64656</EmpID> <EmpID>32487</EmpID> </Groups> <Groups GroupName = "ITDEV"> <EmpID>47312</EmpID> <EmpID>88451</EmpID> <EmpID>48165</EmpID> <EmpID>34567</EmpID> </Groups></Directory>')SELECT R.N.query('.').value('(/Groups/EmpID/.)[1]','int') AS CustomerIDFROM #TableExample TECROSS APPLY TE.Xmldata.nodes('/Directory/Groups') R(N)[/code]I'm able to pull the top EmpID from each Group, but I actually need to do 2 things. Each EmpID(# can vary) and it's respective GroupName.I've searched hours for a solution, but no luck.Output to be something like this:Department EmployeeID-------------------------Management 45612Management 84512Management 32247Management 91548Management 64656Management 32487ITDEV 47312ITDEV 88451ITDEV 48165ITDEV 34567Mon, 23 Jan 2012 20:34:16 GMTbeefderkyhttp://www.sqlservercentral.com/Forums/Topic1239037-21-1.aspxHey all, I am having some issues with XML and XQuery in SQL. Here is basically what I wantI have some XML like this<a> <b>one</b> <c>two</b></a><d> <b>three</b> <c>four</b></d>.....And I want to write a SQL statement using XQuery that will return the following(column name) (column name)----------------- ------------------one twothree fourAny thoughts?Thu, 19 Jan 2012 13:29:47 GMTSQL STEWIEhttp://www.sqlservercentral.com/Forums/Topic1239295-21-1.aspxHi everyoneI have a question about XML and XSD.In my XML file there are repeating elements. I have normalised the XML into several tables, but now I want to use SSIS/Bulk XML Load to import the data to improve load speeds etc.The problem I have is to specify that the record reference should be used for each repeating "table" element in the XSD.For example, if this is my XML:[code]<Product><RecordReference>0001</RecordReference><ProductForm>BB</ProductForm><Title> <TitleType>01</TitleType> <TitleText>Normandy before 1066</TitleText></Title><Contributor> <SequenceNumber>1</SequenceNumber> <PersonName>Professor David Bates</PersonName></Contributor><Contributor> <SequenceNumber>2</SequenceNumber> <PersonName>Professor J de Wet</PersonName></Contributor></Product>[/code]I have a table called Contributor, with the RecordReference, SequenceNumber and PersonName fields.So how would I change the XSD to "include" the RecordReference in the Contributor element?[code] <xs:element minOccurs="0" maxOccurs="unbounded" name="Product"> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" name="RecordReference" type="xs:unsignedLong" /> <xs:element minOccurs="0" name="ProductForm" type="xs:string" /> <xs:element minOccurs="0" name="Contributor" Reference to RecordReference here> <xs:complexType> <xs:sequence> <xs:element minOccurs="0" name="SequenceNumber" type="xs:unsignedByte" /> <xs:element minOccurs="0" name="PersonName" type="xs:string" /> </xs:sequence> </xs:complexType> </xs:element> </xs:complexType> </xs:element>[/code]any ideas?ThanksFri, 20 Jan 2012 04:24:07 GMTJako de Wethttp://www.sqlservercentral.com/Forums/Topic1238252-21-1.aspxHello all, I'm very new to writing sql for xml output. I'm struggling with how to write the syntax for multiple values. I'm not very familiar with the terminology but I'll do my best to explain. I have a patient table that I need to join to several tables that have multiple values.SELECTP.PatientID AS "PatientInfo/PatientID",P.PatientName AS "PatientInfo/PatientName",P.UnitNumber AS "PatientInfo/UnitNumber",PVS.DateTime AS "PatientInfo/patientVitalSign/vitalSignDateTime",PVS.Vital AS "PatientInfo/patientVitalSign/vitalSign/vital",PVS.Value AS "PatientInfo/patientVitalSign/vitalSign/value"FROM Patient PLEFT JOIN PatientVitalSigns PVSON P.PatientID = PVS.PatientIDFOR XML PATH;The way it is written it will start the patient data over for each new row. I need the patient to only print once then the vital signs repeat as needed like below:<- <patientVitalSign> <vitalSignDateTime>2011-07-11T20:00:00</vitalSignDateTime> - <vitalSign> <vital>Temp</vital> <value>98.2</value> </vitalSign>- <vitalSign> <vital>Pulse</vital> <value>142</value> </vitalSign>- <vitalSign> <vital>Resp</vital> <value>66</value> </vitalSign> </patientVitalSign>- <patientVitalSign> <vitalSignDateTime>2011-06-11T16:00:00</vitalSignDateTime> - <vitalSign> <vital>Temp</vital> <value>98.5</value>Thank you for the help!!! JamieWed, 18 Jan 2012 13:33:41 GMTjamie Gerardohttp://www.sqlservercentral.com/Forums/Topic1238497-21-1.aspxFollowing the XML/XSL transformation using templates now I want to create a kind of "wizard" that works on the background to automatically create an XSL. Anyone with recommendations? Maybe someone has done something similar?For now I will start by saving the text a user enters on a text area on each key stroke. At same time the user can double click on a tree leaf and add a value to the text (name, email, full address, invoice number, etc). When this action adds the corresponding text I will add too the corresponding xsl sentence to the text created on background using the name of the clicked node and its parents. No need to add formatting, for now we do not have that option on the edit area.My goal is that at the end of edit the user can send the message but at same time save the message as a template if he wants. The template is the XSL that has been created dinamically on background.Thu, 19 Jan 2012 01:52:02 GMTyazalpizar_http://www.sqlservercentral.com/Forums/Topic1233235-21-1.aspxFound this article [url=http://www.sqlservercentral.com/articles/MDS/75932/]XML-XSL Transformations in SQL Server[/url] but I wonder if there is another way to accomplish that on SQl Server?Tue, 10 Jan 2012 07:47:26 GMTyazalpizar_http://www.sqlservercentral.com/Forums/Topic1234555-21-1.aspxHi everyone,I've used XML PATH and Elements to generate xml, so far so good. But now I'm thinking on a different approach and see if it is possible.I have several pre-created empty XML files, something like templates. The xml I want to create are exactly like those, and the complexity inside the XML is big, lot of child nodes, attributes, etc.Is there a way I can create an XML but "telling" the SQL Server to use those XML templates as a guide? Does this possibility exists? I've done some research and I can not find anything alike.Thanks!Thu, 12 Jan 2012 01:12:53 GMTyazalpizar_http://www.sqlservercentral.com/Forums/Topic1235884-21-1.aspxDear All, I am facing issue while extracting data from one field. I want to split data into new table with based on XML tags. For example in attached statement I want to separate Kpi, Desc, Min, Max, Rate and Comments fields into new table. Thanks in advance.Fri, 13 Jan 2012 12:05:00 GMTwaheed71http://www.sqlservercentral.com/Forums/Topic1227696-21-1.aspxHi All,Iam having the xml with the data as shown below, declare @x xmlset @x = '<SinterklaasWishlists> <child> <name>Tim</name> <wishlist> <article> <artno>21491269</artno> <description>Crane</description> <price>12.50</price> </article> <article> <artno>21499517</artno> <description>Keyboard</description> <price>10</price> </article> <article> <artno>21521591</artno> <description>Crime Investigation Game</description> <price>9.95</price> </article> </wishlist> </child> <child> <name>Tim2</name> <wishlist> <article> <artno>3145678</artno> <description>Mouse</description> <price>12.50</price> </article> </wishlist> </child></SinterklaasWishlists>'I want to extract the elements of xml using sql query and insert the data into the table as shown below:CHILDNAME ARTICLE_NUMBER DESCRIPTION PRICE--------------- -------------------- ----------------------------------- -----------Tim1 21491269 Crane 12.50Tim1 21499517 Keyboard 10Tim1 21521591 Crime Investigation Game 9.95Tim2 3145678 Mouse 12.50Kindly help me on the sameThu, 29 Dec 2011 02:55:44 GMTrams.prskhttp://www.sqlservercentral.com/Forums/Topic1219855-21-1.aspxHiI'm trying to extract data out of an XML, and by and large am unable to get most of the data. It appears that the XML has two namespaces, and I only seem to be able to get the data out of one of them. I've new to XML and have trialled and errored a few things, but for the life of me cannot get the value of one of the elements.This is the XML:<s:ISMessage xmlns="http://informationsystems/schema/messages" xmlns:s="http://informationsystems/schema/messages"> <s:Source>AuthoritativePublisher</s:Source> <s:DateAndTime>2011-11-16T08:28:39.247Z</s:DateAndTime> <s:ClassID>11005</s:ClassID> <s:Version>1</s:Version> <s:ClassDescription>Dumpstation 1 Unloading Task Commenced</s:ClassDescription> <s:Unloading> <s:UnloadingTaskCommenced> <TrainCycleID>43631</TrainCycleID> <Job>134178</Job> <s:UnloadingEvent> <s:Events xmlns:s="http://controlsystems/schema/Events"> <s:LogixEvent xmlns:s="http://controlsystems/schema/Events"> <s:Source>PLC_ASU</s:Source> <s:DateAndTime>2011-11-16T08:28:39.246Z</s:DateAndTime> <s:ClassID>11005</s:ClassID> <s:Version>1</s:Version> <s:UnloadingEvent> <s:UnloadingOperatorAction> <s:StartTrain> <s:DumpStation>1</s:DumpStation> <s:Destination>1011</s:Destination> <s:SelectionID>15330</s:SelectionID> <s:TaskID>205834</s:TaskID> <s:WagonsProcessed>0</s:WagonsProcessed> <s:TonnesProcessed>46</s:TonnesProcessed> </s:StartTrain> </s:UnloadingOperatorAction> </s:UnloadingEvent> </s:LogixEvent> </s:Events> </s:UnloadingEvent> </s:UnloadingTaskCommenced> </s:Unloading></s:ISMessage>I can get the TrainCycleID and JobID details fine, but I also need to get the DumpStation, Destination, SelectionID, etc, and it seems the "s:" in the elements is confusing me. Anyone able to help?This is the query that I'm using. What am I missing ?WITH XMLNAMESPACES( 'http://informationsystems/schema/messages' as m, 'http://controlsystems/schema/Events' as s, default 'http://informationsystems/schema/messages' ) INSERT INTO @JourneyDetails( TrainCycleID , JobID, EventDateAndTimeZulu, Dumpstation, Destination) SELECT x.value('(TrainCycleID)[1]','int') AS TrainCycleID, x.value('(Job)[1]','int') AS JobID, x.value('(s:Events/s:LogixEvent/s:DateAndTime)[1]','varchar(max)') AS EventDateAndTimeZulu, x.value('(s:Events/s:LogixEvent/s:UnloadingEvent/s:UnloadingOperatorAction/s:StartTrain/s:DumpStation)[1]','varchar(max)') AS Dumpstation, x.value('(s:Events/s:LogixEvent/s:UnloadingEvent/s:UnloadingOperatorAction/s:StartTrain/s:Destination)[1]','varchar(max)') AS Destination, FROM @xml.nodes('//UnloadingTaskCommenced') v(x)Sat, 10 Dec 2011 14:27:51 GMTplutekplutekhttp://www.sqlservercentral.com/Forums/Topic1215478-21-1.aspxI have an image data type I would like to select into my XML document. When I select using query analyzer there is data there and formatted just as I would expect. However, when I put it into the FOR XML Path selection it comes up blank and the related element is not there at all. Can anyone tell me how to correct this? It is likely I need a slightly different FOR XML command but I can't figure out what I am missing![code="sql"]select opt.AnswerText as "answerOption/text", opt.AnswerAbbr as "answerOption/abbr", opt.AnswerValue as "answerOption/value", opt.Ordinal as "answerOption/ordinal", opt.RelatedImage as "answerOption/image/imgByte", it.ColumnValue as "answerOption/image/imgType", a.LongName as "attribute/longName", a.ShortName as "attribute/shortName", a.Information as "attribute/information"from rcs.tblAttributeReview_Link ar inner join rcs.tblAttribute a on ar.AttributeUID = a.AttributeUID left join rcs.tblAnswerOption opt on a.AttributeUID = opt.AttributeUID left join rcs.tblLookup it on opt.ImageType = it.LookupID FOR XML Path('reviewAttribute')[/code]which produces:[code="xml"]<reviewAttribute> <id>4</id> <answerOption> <id>8</id> <text>I need an answer</text> <value>0</value> <ordinal>0</ordinal> <enabled>0</enabled> <image> <imgByte></imgByte> </image> </answerOption> <attribute> <id>2EDD5095-550E-430E-B55C-1055D29364BB</id> <longName>Attribute for Review</longName> <shortName>Review</shortName> <answerType> <id>1</id> <columnValue>Free Text</columnValue> </answerType> <class> <id>14</id> <columnValue>Response</columnValue> </class> <state> <id>17</id> <columnValue>Edit</columnValue> </state> <enabled>1</enabled> <information>Some information that is interesting</information> </attribute></reviewAttribute>[/code]It would be perfect if only the imgByte data actually showed up! And there should be an imgType as well that exists when I select the data as data but is not showing up in the XML :(In addition to the issue above I want to have the entire thing start with [code="xml"]<reviewAttribute xmlns="urn:reviewset-1.0">[/code]I have found ways to state the namespace but the formatting is never right. If I try to add xmlns as an attribute to reviewAttribute SQL Server kicks an error that xmlns is not allowed as an attribute. If anyone knows how to get exactly this format as the namespace declaration I would really appreciate the pointer!Thank you!Fri, 02 Dec 2011 09:20:18 GMTevaleahhttp://www.sqlservercentral.com/Forums/Topic1228771-21-1.aspxHello,I am using SQL Server 2008 R2. I have a requirement where I need to update one XML attribute value using another XML's same attribute's value.XML structure is like:[code="xml"]<Data> <Items StatusId="10"> <Item Id="3FF6AD1F-D6F0-43E4-8294-59D678DF478A" Name="HD 1" > <ExtraColumns> <Column RecordType="Sub" IsRecordExist="1" /> </ExtraColumns> </Item> <Item Id="0F88289C-CCD4-47AB-AFC7-54A7F83A1019" Name="HD 2" > <ExtraColumns> <Column RecordType="Sub" IsRecordExist="1" /> </ExtraColumns> </Item> </Items></Data>[/code]I would like to change the "IsRecordExist" attribute value using another XML (which is generated by other process) - same structure. We can use "Id" column as Primary Key. Is there any way to do the same?Sun, 01 Jan 2012 23:00:46 GMTHardy21http://www.sqlservercentral.com/Forums/Topic1226853-21-1.aspxHello everyone, I'm a newbie and didn't knew where exactly to post this thread. I finally decided to post on XML forum as I believe it is related with it. Here is my question(s):I wanted to create a procedure that will receive a string and ouput another string, more or less like this:Input parameter: "Dear {client_id} your order {order_Id} with the enclosed {products_Id} has been shipped"Result: "Dear 'Yaroslav' your order 'Books on SQL Server' with the enclosed 'XML on Sql, SQL Tuning, SQL administration' has been shipped."I began the analysis and thought on 2 tables, one with the tokens and another one with templates of the emails or messages.Tables would be:[code="sql"]CREATE TABLE [dbo].[Token]( [TokenPk] [int] NOT NULL, [Name] [nvarchar](50) NOT NULL, --this is the string to be used on the email: {client_id} for example [Value] [nvarchar](50) NOT NULL, --this value will be the one used, but on my case, is a table.column reference: for {client_id} the value could be {clients.client_name} [Description] [nvarchar](max) NULL, --generic description of the token CONSTRAINT [PK_Token] PRIMARY KEY CLUSTERED ( [TokenPk] ASC) ON [PRIMARY]CREATE TABLE [Clients].[DefaultMessage]( [DefaultAnswerPk] [int] IDENTITY(1,1) NOT NULL, [Text] [nvarchar](max) NULL, --text of the message [PriorityOrder] [int] NOT NULL, --there could be several messages available for same question, so we need a priority order [DefaultMessageCategoryFk] [int] NOT NULL, --points to another table where we have several message categories CONSTRAINT [PK_DefaultAnswer_1] PRIMARY KEY CLUSTERED ([DefaultAnswerPk] ASC) ON [PRIMARY][/code]After giving second thoughts I believed XML/XSD/XSL could be handy here. The templates instead of plain text could be XML templates. Also I could have XSL files attached so we could create different formats for same email. And finally use XSD to validate the data. Even could use XSLT to transform the data and output pdf, html, doc, etc. Then I began to read anything related with XML on SQL Server as I want to process as much as I can using TSQL sentences. I've read all the XML workshop series by Jacob Sebastian and done several testings, more still to do.My questions are: - Is this "achiveable" on TSQL?- Has someone done something alike? Does not matter if it was using plain text, maybe it can help bringing some light to the matter- Wich should be my first steps towards creating a first small working procedure/function using XML to solve this?More questions will arise for sure, but now I'm somewhat lost on where to continue...Tue, 27 Dec 2011 05:28:13 GMTyazalpizar_http://www.sqlservercentral.com/Forums/Topic1222082-21-1.aspxI need an XML element that has an attribute and an element value. The result required is such:[i] <Party> <CustomerID Role="SENDER">1122334455</CustomerID> <Name>SomeOne</Name> <Address1>SomeWhere</ADDRESS> <City>SomeCity</City> </Party> [/i] This is a subquery in part of a larger SQL XML query, the data is static so no table is referenced. The DB is SQL Server 2005.The query I am using is:([b] SELECT 'SENDER' 'CustomerID/@Role', '1122334455' 'CustomerID', 'SomeOne' 'Name', 'SomeWhere' 'Address1', 'SomeCity' 'City') FOR XML path ('Party') [/b]But the result is:[i]<Party> <CustomerID Role="SENDER"> </CustomerID> <Name>SomeOne</Name> <Address1>SomeWhere</Address1> <City>SomeCity</City></Party>[/i]Any ideas on what is missing or wrong with my query?Wed, 14 Dec 2011 15:04:36 GMTBrent Seeney-273128http://www.sqlservercentral.com/Forums/Topic1163138-21-1.aspxHello,I have created a stored procedure which contains lines like below: if @int = '0' begin select @alert = (select replace(@alert,'{' + CAST(@int AS CHAR(1)) + '}',@N1.value('AlertParameters[1]/AlertParameter1[1]','varchar(max)'))) endand if @int = '1' begin select @alert = (select replace(@alert,'{' + CAST(@int AS CHAR(1)) + '}',@N1.value('AlertParameters[1]/AlertParameter2[1]','varchar(max)'))) endIt works but it's not really nice coding. I would like the 'AlertParameters[1]/AlertParameter[b]1[/b][1]' part to be dynamic so that I don't have to use an 'if begin ... end' statement for all possible values of @int.How can I achive this?Best regards,Coen van DijkMon, 22 Aug 2011 01:56:12 GMTcoen.van.dijkhttp://www.sqlservercentral.com/Forums/Topic1213116-21-1.aspxIn another thread, the question came up to find all dependancies of an executed SQL statement.Looking over some sample execution plans, I found it's not something that is simple, although all the data is in the execution plan.i can see that a sqlplan file will have multiple <RelOp><OutputList><ColumnReference> tags, all at different levels within the plan.I know how to use xquery to drill down to a specific tag, all the way from the top, like my script below,but my problem is there are multiple <RelOp><OutpoutList><ColumnReference> nodes, some under nested loops, others under other tags, all depending on the plan itself.how can i select <RelOp><OutpoutList><ColumnReference> nomatter what node/level in the xml it sits under?[code]WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)SELECT p.query_plan, DatabaseName = p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Database)[1]', 'NVARCHAR(256)'), TableName = p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Table)[1]', 'NVARCHAR(256)'), SchemaName = p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Schema)[1]', 'NVARCHAR(256)'), ColumnName = p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Column)[1]', 'NVARCHAR(256)'), Alias = p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Alias)[1]', 'NVARCHAR(256)')FROM sys.dm_exec_query_stats s CROSS APPLY sys.DM_EXEC_QUERY_PLAN(s.plan_handle) AS p WHERE p.query_plan.exist(N'/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference') = 1 [/code]Tue, 29 Nov 2011 05:38:14 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1211546-21-1.aspxI have to do a lot of work on an SQL database that has some issues carried forward from an old legacy database, it still uses line typing within the same table.I am trying to extract data as XML from these tables.Briefly, the OrderDetails table can contain either stock lines or comment lines.The desired result is:<SalesOrders xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Orders> <OrderHeader> <XOrder>WKMH10000 </XOrder> <CustomerPoNumber>1234</CustomerPoNumber> <Customer>BOL1000</Customer> <OrderDate>2011-10-10</OrderDate> <RequestedShipDate>2011-10-15</RequestedShipDate> </OrderHeader> <OrderDetails> <StockLine> <CustomerPoLine>1</CustomerPoLine> <LineActionType>A</LineActionType> <StockCode>WS8x30</StockCode> <OrderQty>1200.000</OrderQty> <Price>5.23000</Price> </StockLine> <CommentLine> <CustomerPoLine>2</CustomerPoLine> <LineActionType>A</LineActionType> <Comment>This is a comment line</Comment> <AttachedLineNumber>1</AttachedLineNumber> <CommentType>A</CommentType> </CommentLine> <StockLine> <CustomerPoLine>3</CustomerPoLine> <LineActionType>A</LineActionType> <StockCode>WS8x40</StockCode> <OrderQty>800.000</OrderQty> <Price>5.50000</Price> </StockLine> </OrderDetails> </Orders> </SalesOrders>I get this without the CustomerOrderLine2 (The comment line) by using this:select XOrder as [OrderHeader/XOrder],[CustomerPoNumber] as [OrderHeader/CustomerPoNumber], Customer as [OrderHeader/Customer], CONVERT(VARCHAR(10),OrderDate,120) as [OrderHeader/OrderDate], CONVERT(VARCHAR(10),RequestedShipDate,120) as [OrderHeader/RequestedShipDate], (Select (select CustomerPoLine as [StockLine/CustomerPoLine], 'A' as [StockLine/LineActionType], StockCode as [StockLine/StockCode], OrderQty as [StockLine/OrderQty], Price as [StockLine/Price] from OrderDetails where OrderDetails.XOrder = OrderHeaders.XOrder AND LineType = 1 for xml path(''),type) ) as OrderDetailsfrom OrderHeaders for xml path('Orders'),elements XSINIL,root('SalesOrders')If I vary it slightly I get the CommentLine. Like this:select XOrder as [OrderHeader/XOrder],[CustomerPoNumber] as [OrderHeader/CustomerPoNumber], Customer as [OrderHeader/Customer], CONVERT(VARCHAR(10),OrderDate,120) as [OrderHeader/OrderDate], CONVERT(VARCHAR(10),RequestedShipDate,120) as [OrderHeader/RequestedShipDate], (Select (select CustomerPoLine as [CommentLine/CustomerPoLine], 'A' as [CommentLine/LineActionType], Comment as [CommentLine/Comment], AttachedLineNumber as [CommentLine/AttachedLineNumber], CommentType as [CommentLine/CommentType] from OrderDetails where OrderDetails.XOrder = OrderHeaders.XOrder AND LineType = 6 for xml path(''),type) ) as OrderDetailsfrom OrderHeaders for xml path('Orders'),elements XSINIL,root('SalesOrders')I have tried various ways of using CASE or sub-queries but with no success.Does anybody out there have a solution for me?Thanks, KenThu, 24 Nov 2011 04:13:35 GMTken-1133369http://www.sqlservercentral.com/Forums/Topic1211217-21-1.aspxI have the following query:[code="sql"]select r.ReviewUID as "id", r.RTitle as "title", r.RDescription as "description", r.CreatedDateUTC as "createdDate", r.ImplementationDate as "implementationDate", r.EffectiveDateUTC as "effectiveDate", r.StateID as "state/id", st.ColumnInt as "state/columnInt", st.ColumnValue as "state/columnValue", (select n.AttachType as "attachType/id", at.ColumnInt as "attachType/columnInt", at.ColumnValue as "attachType/columnValue", n.NoteText as "text" from rcs.tblNotes n left join rcs.tblLookup at on n.AttachType = at.LookupID where n.EntityUID = r.ReviewUID FOR XML Path('note')) as "notes"from rcs.tblReview rleft join rcs.tblLookup st on r.StateID = st.LookupIDwhere r.ReviewUID = '4412567C-7EFD-41E1-BAC2-747790C99018'FOR XML Path('review')[/code]which returns almost exactly what I need:<review>[code="xml"] <id>4412567C-7EFD-41E1-BAC2-747790C99018</id> <title>FancyNewName</title> <description>A lot to say about this test review</description> <createdDate>2011-11-23T12:06:17</createdDate> <implementationDate>2011-11-27T12:06:17</implementationDate> <effectiveDate>2011-11-27T12:06:17</effectiveDate> <state> <id>17</id> <columnInt>1</columnInt> <columnValue>Edit</columnValue> </state> <notes>& lt;note>& lt;text& gt;An addition note to add to the review& lt;/text& gt;& lt;/note& gt;</notes></review>[/code]Only I do not want the "<" and ">" characters to be escaped for html. I want them raw. How do I alter what I have above to get exactly this only really as just xml?PLEASE NOTE: The spaces in the html escape characters were added so this would show up correctly in this post. Otherwise, the parsers were showing what was being escaped. The spaces do not actually exist in my output.Thanks!Wed, 23 Nov 2011 10:46:17 GMTevaleahhttp://www.sqlservercentral.com/Forums/Topic1209742-21-1.aspxHello,I'll try and keep this simple.I have an XML file, 2 nodes of which I'm concerning about. A 'Supplier' Node listing their products, and an 'Attributes' node. Simply put, there are attributes to every Product produced by a supplier.There's only one field that could possibly link the two together, and that's Product_ID. However, multiple suppliers will have a product_id of 1, thus making the join in T-SQL tables impossible.Does SSIS in BIDS (which I'm relatively new too) have a function/feature/transformation, where by I can populate an Attributes table with all of it's corresponding data, while also inserting the supplier_id as an extra column.The supplier_id needs to come from the supplier node, while 'joining' the attributes table; so to speak.You would think this is somehow possible, based on the fact that the attributes node is always a node within the supplier node.Any help or guidance would be much appreciated.Cheers,-RoMon, 21 Nov 2011 17:55:12 GMTrharehttp://www.sqlservercentral.com/Forums/Topic1207959-21-1.aspxGood people:Here is my situation. I have an ASP.NET Form with hundreds of input boxes that hold values for individual records. I have a mechanism where I use the table key to name each textbox. So, in the VB code, I can create an XML variable from this data. It is my intention to pass it to the database via a stored procedure that looks something like this:[code="sql"]create proc spLoadIt ( @xml XML)asbegin ???end[/code]The question marks are where I am having issues. In fact, there are two of them, and I would really appreciate any help on either.1) I would like to construct the XML in the following fashion:[code="xml"]<mytable keya="101" keyb="A" keyc="1" value="8.33"><mytable keya="102" keyb="A" keyc="3" value="22.5"><mytable keya="103" keyb="M" keyc="4" value="0.18">...[/code]In other words, have each element represent a single row and the attributes represent the columns -- a 3-part key and the value to store. Is this feasible?2) In the upsert procedure, I would like to use the MERGE statement to make the insert/update procedure quick and painless. Unfortunately, I lack the right knowledge to both put something together and find what I need online. I get the basic syntax of the MERGE, but I don't know how to map the attributes in the elements to fields for use in the T-SQL code. Anyone know how to do this? My head is aching from all the google searching.Thu, 17 Nov 2011 14:55:39 GMTjames.jensen1350