extract xml data to fields

Question

I am trying to extract the data from the following xml via a stored procdure.

<my:Action_Group xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2006-03-03T23:07:29">
  <my:Actions>
    <my:Action_A />
    <my:group20>
      <my:group21>
        <my:field48>22000037</my:field48>
        <my:ActionBy>Warehouse</my:ActionBy>
        <my:ActionDate>2012-04-06</my:ActionDate>
        <my:field55 />
        <my:field56 />
        <my:Action_Type>Preventative</my:Action_Type>
        <my:ActionDescription>rerty erty ertye</my:ActionDescription>
        <my:Completed>no</my:Completed>
      </my:group21>
      <my:group21>
        <my:field48 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
        <my:ActionBy>Bobby Fr</my:ActionBy>
        <my:ActionDate>2012-04-06</my:ActionDate>
        <my:field55 />
        <my:field56 />
        <my:Action_Type>Corrective</my:Action_Type>
        <my:ActionDescription>dfghd rty drty ert</my:ActionDescription>
        <my:Completed>no</my:Completed>
      </my:group21>
    </my:group20>
  </my:Actions>
  <my:field54 />
</my:Action_Group>

I have found this in the forums, but i can't get it to work. (I receive no data)

DECLARE @xml XML

SET @xml = (select top 1 Converted from dbo.TempXMLData)
select @xml

;WITH XMLNAMESPACES(

    'http://schemas.microsoft.com/office/infopath/2003/myXSD/2006-03-03T23:07:29' AS my )

SELECT
    x.y.value('(my:field48/text())[1]', 'INT') AS RecordID,
    x.y.value('(my:ActionBy/text())[1]', 'VARCHAR(50)') AS ActionWho,
    x.y.value('(my:ActionDate/text())[1]', 'VARCHAR(100)') AS ActionDate,
    x.y.value('(my:field55 /text())[1]', 'INT') AS NotUsed1,
    x.y.value('(my:field56 /text())[1]', 'VARCHAR(100)') AS Notused2,
    x.y.value('(my:Action_Type/text())[1]', 'VARCHAR(100)') AS ActionType,
    x.y.value('(my:ActionDescription/text())[1]', 'VARCHAR(100)') AS txtPhone2,
    x.y.value('(my:Completed/text())[1]', 'VARCHAR(10)') AS Completed


--FROM @xml.nodes('my:Action_Group/my:Actions/my:Action_A /my:group20/my:group21') x(y)
FROM @xml.nodes('my:group21') x(y)

Can someone help...

Answer 1

Either of the following from clauses will work (I prefer the first):

FROM @xml.nodes('my:Action_Group/my:Actions/my:group20/my:group21') x(y)
FROM @xml.nodes('//my:group21') x(y)

You added the node reference to my:Action_A which is not an ancestor to my:group21.

---

Russel Loski, MCT, MCITP Business Intelligence Developer and Database Developer 2008

Answer 2

You sir, are a Rock Star!!!!!  Thank you so very much.