• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

XML Datatype

I have a solution graciousely supplied by BCUNNEY for handling some XML Data.  See attached code.

I have 2 actual questions...

Is there a "character length" for the xml data type?

How would I handle things if my select in the attached exceeded the varchar(max)  ...8000? character length?
CREATE TABLE XmlImportBuffer  
(  
        xmlFileInfo VARCHAR(300) NOT NULL,  
        xml_data XML NOT NULL  
)  
GO 

DECLARE @xmlFileName VARCHAR(300)  
SELECT @xmlFileName = 'c:\testXML.xml'  
  
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET  
  
EXEC('INSERT INTO XmlImportBuffer(xmlFileInfo, xml_data)  
  
SELECT ''' + @xmlFileName + ''', xmlData  
FROM(  
SELECT *  
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA  
) AS FileImport (XMLDATA)  
')  
GO 


declare @xml_text varchar(max)          -- variable to store the XML content  
declare @i int                                          -- xml document handle  
  
-- get XML content from XML data type field in table into a variable  
select @xml_text = cast(xml_data as varchar(max)) from xmlImportBuffer  
  
-- prepare the XML document  
exec sp_xml_preparedocument @i OUTPUT, @xml_text  
  
-- Get the PacketNum and RunDate at the <Document> node level  
-- and rpeat these fields(CROSS APPLY) for every record at the <Record> node level  
select * from  
        openxml(@i,'/Document',2)  
        with  
        (  
                PacketNum nvarchar(100),  
                RunDate bigint  
        )  
CROSS APPLY  
(  
        select * from  
        openxml(@i,'/Document/Record',2)  
        with  
        (  
                SeqNumber int,  
                LineRefNumber bigint,  
                Ref2 nvarchar(50),  
                FilingOffice nvarchar(50)  
        )  
) s  
CROSS APPLY  
(  
        select * from  
        openxml(@i,'/Document/Record/FirstDebtorName/IndividualName',2)  
        with  
        (  
                FirstName nvarchar(50),  
                LastName nvarchar(50)  
        )  
) d 

 
  
-- remove the XML dcoument  
exec sp_xml_removedocument @i

Open in new window

0
lrbrister
Asked:
lrbrister
1 Solution
 
Kobe_LenjouCommented:
varchar(max) is virtually unlimited in size ... the 8000 limit does not apply
0
 
kumarnimavatCommented:
The maximum size for XML datatype is 2 GB and stored like varbinary.

For your 2nd question, you may refer to the link given below.

http://forums.devshed.com/ms-sql-development-95/how-to-exceed-varchar-max-limit-490931.html
http://stackoverflow.com/questions/564755/sql-server-text-type-v-s-varchar-data-type
0
 
lrbristerAuthor Commented:
Great!  Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now