Tutorial :SQL Server 2005 XML to table



Question:

I'm using classic asp and I'm passing in a varchar (ado data type) into an xml data column in a proc.

The xml looks as follows:

<DocumentCriteria>      <Document documentGUID="{B49654E7-9AF2-4B89-AF8F-5146F7CD4911}" graderFYC="5907">         <criterion cnumber="1" rank="3"/>        <criterion cnumber="3" rank="3"/>     </Document>  </DocumentCriteria>'  

I'd like to have a table that looks like this:

DocumentGUID|GraderFYC|Cnumber|Rank  {guid}      | 5907    | 1     | 3  {guid}      | 5907    | 3     | 3  

How can I do this within a procedure? Is the adVarchar type okay in the ADO connection?


Solution:1

So you have a stored proc that takes a VARCHAR? OK, something like this will work:

ALTER PROCEDURE dbo.YourProcedure(@data VARCHAR(2000))  AS BEGIN       DECLARE @XmlContent XML     SET @XmlContent = CAST(@data AS XML)       INSERT INTO dbo.YourTable(DocumentGUID, GraderFYC, Cnumber, Rank)        SELECT           @XmlContent.value('(/DocumentCriteria/Document/@documentGUID)[1]', 'Varchar(50)'),           @XmlContent.value('(/DocumentCriteria/Document/@graderFYC)[1]', 'int'),           Doc.Crit.value('(@cnumber)[1]', 'int') AS 'CNumber',           Doc.Crit.value('(@rank)[1]', 'int') AS 'Rank'        FROM           @XmlContent.nodes('/DocumentCriteria/Document/criterion') AS Doc(Crit)  END  

This selects the DocumentGUID and GraderFYC from your single <Document> tag and then iterates over all contains <criterion> nodes to grab the rest of the data.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »