asked on

Check for XML data type variable

I have an sp that has a variable of type XML I want to check to make sure it was passed in correctly? the IF LEN(@Data) > 0 proc=duces the following Error
Argument data type xml is invalid for argument 1 of len function.

Any help is greatly appreciated.

CREATE PROCEDURE [dbo].[RunXML_CreateTempValidateRemovalTubeLocations]
      -- Add the parameters for the stored procedure here
      @Data XML
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

-- Insert statements for procedure here

-- Using the value() method

      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TempValidateRemovalTubeData]') AND type in (N'U'))
      DROP TABLE [dbo].[TempValidateRemovalTubeData]

      IF LEN(@Data) > 0
      BEGIN
            SELECT T.Tube.value('@bc', 'VARCHAR(50)') AS bc,
                   T.Tube.value('@RackBc', 'VARCHAR(50)') AS RackBc,
                   T.Tube.value('@Row', 'Int') AS Row,
                   T.Tube.value('@Col', 'Int') AS Col
            INTO TempValidateRemovalTubeData
            FROM @Data.nodes('Tubes/Tube') AS T(Tube);
      END
      ELSE

      BEGIN
      CREATE TABLE [dbo].[TempValidateRemovalTubeData](
            [bc] [varchar](50) NULL,
            [RackBc] [varchar](50) NULL,
            [Row] [int] NULL,
            [Col] [int] NULL
      ) ON [PRIMARY]

      END

      SELECT * FROM TempValidateRemovalTubeData
END

Bodestone

If you cast it to NVARCHAR(MAX) is should be fine (or if you know it is a certain length less than 4000 use that)

IF LEN(CAST(@Data AS NVARCHAR(MAX))) > 0

Pavel Celba

If you need to know length of XML data then you have to use

DATALENGTH() function

Unfortunately, it will say nothing about XML data validity because you may store almost any text into XML columns.

ASKER CERTIFIED SOLUTION

Vacheron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bodestone

OK, so I am fairly new to XML stuff in SQL too.
OK, barely starting but had seen that method for looking up specific elements but had not thought of this way yet.
Just to see how it worked I threw together the following and...
Very handy indeed.
Cheers Vacheron.

DECLARE @data1 XML, @data2 XML, @data3 XML, @data4 XML
SET @data1 = '<hello>there</hello>'
SET @data2 = 'hello there'
SET @data3 = ''
SELECT @data1.exist('*'), @data2.exist('*'), @data3.exist('*'), @data4.exist('*')

Open in new window

Pavel Celba

Yes, it should work :-)

Pavel Celba

You could even test

DECLARE @data1 XML, @data2 XML, @data3 XML, @data4 XML
SET @data1 = '<hello>there</hello>'
SET @data2 = '<hello>there<and>anywhere</and></hello>'
SELECT @data1.exist('/hello'), @data1.exist('/hello/and'), @data2.exist('/hello/and')

Bodestone

Aye. I was just testing results of * with valid data, plain text, zero text and null

Pavel Celba

I am learning also.

Thanks to Vacheron.

TraciShultz

ASKER

Perfect just what I was looking for. Thanks for all the comments...