Querying an XML String inside of a Stored Procedure

Question

I am currently generating a Report using a Stored Procedure. The stored procedure gathers information about Unprocessed Files from a table of Files that resembles the following (simplfied for brevity):

Table Structure:

//File Table Fields
FILE_ID (int)
FILE_DATE (string)
FILE_CONTENTS (XML)
FILE_CONTENTS_STRING (string)
PROCESSED (bool)

Stored Procedure:

//Grabs the ID and Date for each of the Unprocessed Files
SELECT [FILE_ID],
       [FILE_DATE]
  FROM [tbFILES]
 WHERE [PROCESSED] = 0

I would like to output two additional fields that are found in the FILE_CONTENTS (or FILE_CONTENTS_STRING), which are both found in the XML / String in areas like these:

Portion of XML Structure:

    <FID.4>
      <FID.4.1>TESTING</FID.4.1>     //File Header
    </FID.4>
    <FID.5>
      <FID.5.1>TEST</FID.5.1>        //Owner Last Name
      <FID.5.2>TEST</FID.5.2>        //Owner First Name
      <FID.5.3 />
      <FID.5.4 />
      <FID.5.5 />
      <FID.5.6 />
      <FID.5.7 />
      <FID.5.8 />
    </FID.5>

What I would like to accomplish is to output these two values (File Header) and Owner Name (Last,First) as part of the Stored Procedure Call.

Output:

[FILE_ID]      //From Table
[FILE_DATE]    //From Table
[FILE_HEADER]  //From FILE_CONTENTS in <FID.4.1></FID.4.1>
[FILE_OWNER]   //From FILE_CONTENTS in <FID.5.1></FID.5.1>,<FID.5.2></FID.5.2>

Is it possibly to query this type of information from the XML File (FILE_CONTENTS) or the XML File in string-form (FILE_CONTENTS_STRING) using a SQL Stored Procedure?

Edit: First Attempt (unsuccessful)

SELECT FILE_ID,
       FILE_DATE,
       FILE_CONTENTS.value('(/PID.4/PID.4.1)[1]', 'varchar(16)') as FILE_HEADER,
       FILE_CONTENTS.value('(/PID.5/PID.5.1)[1]', 'varchar(16)') + ', ' +     
       FILE_CONTENTS.value('(/PID.5/PID.5.2)[1]', 'varchar(16)') as FILE_OWNER
  FROM [tbFILES]

which yielded NULL for the FILE_HEADER and FILE_OWNER fields. I presume that something more complex is required?

Update again: (Problem Solved!)

I had to add an additional section to the XML, as the portion provided was originally a snippet of a larger XML document.

marc_s · Accepted Answer · 2011-04-14 05:05:49Z

1

Try this query here - does that a) work and b) do what you're looking for??

-- test data setup
DECLARE @test TABLE (FILE_ID INT, FILE_DATE DATETIME, FILE_CONTENTS XML)

INSERT INTO @test VALUES(4711, '20110414', 
'<FID.4>
      <FID.4.1>TESTING</FID.4.1>     //File Header
    </FID.4>
    <FID.5>
      <FID.5.1>TEST_LN</FID.5.1>        //Owner Last Name
      <FID.5.2>TEST_FN</FID.5.2>        //Owner First Name
      <FID.5.3 />
      <FID.5.4 />
    </FID.5>')

-- select the values from the table and cross apply bits from the XML   
SELECT 
     FILE_ID ,
     FILE_DATE ,
     Node.value('(/FID.4/FID.4.1/text())[1]', 'varchar(50)') AS 'File_Header',
     Node.value('(/FID.5/FID.5.1/text())[1]', 'varchar(50)') + ', ' +
     Node.value('(/FID.5/FID.5.2/text())[1]', 'varchar(50)') AS 'File_Owner'
FROM
    @test
CROSS APPLY
    FILE_CONTENTS.nodes('/*') AS Content(Node)

My output looks like this:

FILE_ID   FILE_DATE                File_Header   File_Owner
 4711     2011-04-14 00:00:00.000   TESTING      TEST_LN, TEST_FN
 4711     2011-04-14 00:00:00.000   TESTING      TEST_LN, TEST_FN

If your XML had a proper root element, you could probably makes this even better (in terms of performance) - use the proper XPath in the CROSS APPLY condition to grab those bits of repeating nodes from the XML that you want.

answered Apr 14, 2011 at 5:05

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rion Williams Over a year ago

Hi Marc- I appreciate your answer, and when I tried to use it I received NULL values in both the File_Header and File_Owner fields. I'll keep trying slight variations in hope of making some progress.

Rion Williams Over a year ago

Just an update Marc - I was able to get my original method to work (just using the value), apparently I had to look a bit further and add an additional level onto the Node.value section. I appreciate your help :)

Collectives™ on Stack Overflow

Querying an XML String inside of a Stored Procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related