asked on

Query XML data in TSQL

Hello,
I'm trying to access an xml parameter as rows in TSQL. The proc is in code section. The parameter value I pass is:

<dsPersonalstatistik><tblPersonalstatistik><ERPBez>30</ERPBez></tblPersonalstatistik><tblPersonalstatistik><ERPBez>31</ERPBez></tblPersonalstatistik></dsPersonalstatistik>

The desired output:

c1 c2
30 x
31 x

Unfortunately c1 contains only NULL values. Any hints?

ALTER PROCEDURE sp_Import__Personaldata (
			@Content xml
			/*@LastChangeUserID uniqueIdentifier,
			@MandantID uniqueIdentifier */
			)
 AS 
	BEGIN
		SET NOCOUNT ON
		DECLARE @ErrMSG nvarchar(1000)
		BEGIN TRY
			BEGIN TRANSACTION
				BEGIN
					SELECT 
						ref.value('@ERPBez' , 'varchar(20)') AS c1,
						'x' as c2
					FROM  @Content.nodes('//dsPersonalstatistik/tblPersonalstatistik') AS R(ref)
 
				END
			COMMIT
		END TRY
		BEGIN CATCH
			SET @ErrMSG = Error_Message()
			raiserror(@ErrMSG,16,1)
			ROLLBACK TRANSACTION
			RETURN
		END CATCH
	END

Open in new window

Pavel Celba

You may try sp_xml_preparedocument and sp_xml_removedocument. Hope BEGIN TRAN and COMMIT is for future use in your code because to enclose simple SELECT command into a transactions does not make sense here.

ALTER PROCEDURE sp_Import__Personaldata ( 
    @Content xml 
  /*@LastChangeUserID uniqueIdentifier, 
    @MandantID uniqueIdentifier */ 
   ) 
AS  
BEGIN 
  SET NOCOUNT ON 
  DECLARE @ErrMSG nvarchar(1000) 
  BEGIN TRY 
    BEGIN TRANSACTION 
      BEGIN 
        DECLARE @xmlID int;
        EXEC sp_xml_preparedocument @xmlID OUTPUT, @Content; 
        SELECT Dta.ERPBez C1, 'x' C2
          FROM OPENXML (@xmlID, '/dsPersonalstatistik/tblPersonalstatistik',1) 
                  WITH (ERPBez  varchar(20) 'ERPBez') Dta;
        EXEC sp_xml_removedocument @xmlID;
      END 
    COMMIT 
  END TRY 
  BEGIN CATCH 
    SET @ErrMSG = Error_Message() 
    raiserror(@ErrMSG,16,1) 
    ROLLBACK TRANSACTION 
    RETURN 
  END CATCH 
END

Open in new window

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

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

Start Free Trial

Stephan_Schrandt

ASKER

Thank you, working perfectly.