T-sql to parse XML nodes with special elements

Question

My goal is to break out this xml so I can take the Field elements for each Record Row and insert into a SQL table. I cannot figure out how to traverse the Grids hierarchy at all. I have been able to run a few xquery commands against it, but only at the highest level as shown below. Please help to get all the way down do the Field Element values for each field for each Row.

declare @Responsetext varchar(8000)
set @Responsetext =
 '<GridResponse xmlns="http://abcd.abcd.net/aapi/2009/08/">
  <Brand id="1111">API Starter App</Brand>
  <User>AAA_API</User>
  <Grids>
    <Grid type="subscriber">
      <Record row="1">
        <Fields>
          <Field element="subscriber_id">111107293</Field>
          <Field element="bounce_date" />
          <Field element="cancellation_mailing_instance_id" />
          <Field element="cancellation_message" />
          <Field element="cancellation_date" />
          <Field element="email">aaaa4@gmail.com</Field>
          <Field element="is_repeated_bouncer">0</Field>
          <Field element="is_unsubscriber">0</Field>
          <Field element="modified_date">2015-04-07T13:19:09.3400000Z</Field>
          <Field element="service_since_date">2011-10-17T13:23:38.7800000Z</Field>
          <Field element="user_id" />
        </Fields>
      </Record>
      <Record row="2">
        <Fields>
          <Field element="subscriber_id">111169135</Field>
          <Field element="bounce_date" />
          <Field element="cancellation_mailing_instance_id" />
          <Field element="cancellation_message" />
          <Field element="cancellation_date" />
          <Field element="email">aaaa.bass@aaa.org</Field>
          <Field element="is_repeated_bouncer">0</Field>
          <Field element="is_unsubscriber">0</Field>
          <Field element="modified_date">2014-12-15T17:30:18.2230000Z</Field>
          <Field element="service_since_date">2011-10-19T14:11:26.6370000Z</Field>
          <Field element="user_id" />
        </Fields>
      </Record>
    </Grid>
  </Grids>
</GridResponse>
'

This code gives me the node tree, but I cannot figure out how to tweak this to isolate the Field Values that are 8 levels deep.

--This CTE gives you the Node Tree
WITH Xml_CTE AS
(
    SELECT
        CAST('/' + node.value('fn:local-name(.)',
            'varchar(100)') AS varchar(100)) AS name,
        node.query('*') AS children
    FROM @XmlResponse.nodes('/*') AS roots(node)

    UNION ALL

    SELECT
        CAST(x.name + '/' + 
            node.value('fn:local-name(.)', 'varchar(100)') AS varchar(100)),
        node.query('*') AS children
    FROM Xml_CTE x
    CROSS APPLY x.children.nodes('*') AS child(node)
)
SELECT distinct name
FROM Xml_CTE x
OPTION (MAXRECURSION 1000)

If I shove the xml into a table instead of using the variable, I can get the root node like this, but still cannot get down to the field level.

--Parent Node -- Returns GridResponse
SELECT 
( 
SELECT 
c.value('local-name(.)', 'nvarchar(50)') 
FROM 
xmldata.nodes('/*') AS r(c) 
) AS RootParent 
FROM [dbo].subscriber_xml

I can query to see if the nodes exist, and get the root and the Brand child node, but not the Grids child node. This is very confusing why one works and the other does not.

SELECT XmlData.exist('(/*[1][contains(local-name(.),"GridResponse")])') 
FROM [dbo].subscriber_xml

SELECT XmlData.exist('(/*/*[1][contains(local-name(.),"Brand")])') 
FROM [dbo].subscriber_xml

SELECT XmlData.exist('(/*/*[1][contains(local-name(.),"Grids")])') 
FROM [dbo].subscriber_xml

Maybe this will help you http://stackoverflow.com/questions/13195922/how-to-query-xml-column-in-tsql?rq=1 — Alexander Baltasar, Oct 26 '15 at 15:12

score 0 · Answer 1 · answered Oct 26 '15 at 16:09

This is my suggestion (Please change your declaration of @Responsetext to XML):

WITH XMLNAMESPACES(DEFAULT 'http://abcd.abcd.net/aapi/2009/08/')
SELECT OneRecord.value('@row','varchar(max)') AS RowID
      ,OneRecord.value('(Fields/Field[@element="subscriber_id"])[1]','varchar(max)') AS subscriber_id
      ,OneRecord.value('(Fields/Field[@element="bounce_date"])[1]','varchar(max)') AS bounce_date
      ,OneRecord.value('(Fields/Field[@element="cancellation_mailing_instance_id"])[1]','varchar(max)') AS cancellation_mailing_instance_id
      ,OneRecord.value('(Fields/Field[@element="cancellation_message"])[1]','varchar(max)') AS cancellation_message
      ,OneRecord.value('(Fields/Field[@element="cancellation_date"])[1]','varchar(max)') AS cancellation_date
      ,OneRecord.value('(Fields/Field[@element="email"])[1]','varchar(max)') AS email
      ,OneRecord.value('(Fields/Field[@element="is_repeated_bouncer"])[1]','varchar(max)') AS is_repeated_bouncer
      ,OneRecord.value('(Fields/Field[@element="is_unsubscriber"])[1]','varchar(max)') AS is_unsubscriber
      ,OneRecord.value('(Fields/Field[@element="modified_date"])[1]','varchar(max)') AS modified_date
      ,OneRecord.value('(Fields/Field[@element="service_since_date"])[1]','varchar(max)') AS service_since_date
      ,OneRecord.value('(Fields/Field[@element="user_id"])[1]','varchar(max)') AS user_id
--INTO tempTable
FROM @Responsetext.nodes('/GridResponse/Grids/Grid/Record') AS Records(OneRecord)

This will show your data in a table grid. Just take away the-- before the INTO tempTable and set there a table name you like. This will create a table with the given structure and fill it with your data.

One more point: You should choose datatypes according to your data. I took varchar(max) for all, but you'll probably want to change this to int, datetime or any other...

@KWhiteside, Hi, I'm glad to read, that this could help you! As you are new to SO, please be aware, that the professionals giving answers here, are hungry for reputation points. It would be very kind of you to vote helpful answers up and - if an answer helped you to solve your problem - you should mark it as the accepted answer, This will show to others, that this question is solved. Thx! — Shnugo, Oct 26 '15 at 20:40

T-sql to parse XML nodes with special elements

1 Answers1