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

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

0
Stephan_Schrandt
Asked:
Stephan_Schrandt
1 Solution
 
pcelbaCommented:
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

0
 
Anthony PerkinsCommented:
You are close.  Change this:
ref.value('@ERPBez' , 'varchar(20)') AS c1,
To:
ref.value('ERPBez[1]' , 'varchar(20)') AS c1,
0
 
Stephan_SchrandtAuthor Commented:
Thank you, working perfectly.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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