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

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
0
TraciShultz
Asked:
TraciShultz
1 Solution
 
BodestoneCommented:
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
0
 
pcelbaCommented:
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.
0
 
VacheronCommented:
Please try this condition:

IF @Data.exist('*') = 1
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 
BodestoneCommented:
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

0
 
pcelbaCommented:
Yes, it should work :-)
0
 
pcelbaCommented:
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')
0
 
BodestoneCommented:
Aye. I was just testing results of * with valid data, plain text, zero text and null
0
 
pcelbaCommented:
I am learning also.

Thanks to Vacheron.
0
 
TraciShultzAuthor Commented:
Perfect just what I was looking for. Thanks for all the comments...
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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