How to insert the xml nodes with same name and ignore based on the attribute

I have a below xml

`

<xml>
<entities>
<entity id="147297" version="20150209161956">
    <dobs>
        <dob Y="1970">10/16/1970</dob>
        <dob D="17" M="10" Y="1970">10/17/1970</dob>
        <dob D="02" M="7" Y="1920">02/1]7/1920</dob>
        <dob D="1" M="9" Y="1990">10/9/1990</dob>
        <dob Y="1870">10/16/1870</dob>
    </dobs>
</entity>
</entities>
</xml>

`

I need to insert only the dob nodes which has D and M and Y attributes in table (Table A), if only Y attribute is specified then the entire tag need to be inserted in another table (Table B), how to achieve this is SQL.

Thanks for your suggestions

Answer

Are you looking for something like this?

DECLARE @xml XML=
N'<xml>
<entities>
<entity id="147297" version="20150209161956">
    <dobs>
        <dob Y="1970">10/16/1970</dob>
        <dob D="17" M="10" Y="1970">10/17/1970</dob>
        <dob D="02" M="7" Y="1920">02/1]7/1920</dob>
        <dob D="1" M="9" Y="1990">10/9/1990</dob>
        <dob Y="1870">10/16/1870</dob>
    </dobs>
</entity>
</entities>
</xml>';

–complete dobs for Table A

SELECT ent.value('@id','int') AS Entity_Id
      ,ent.value('@version','nvarchar(max)') AS Entity_Version
      ,AllParams.query('.')
FROM @xml.nodes('/xml/entities/entity') A(ent)
OUTER APPLY A.ent.nodes('dobs/dob[@D and @M and @Y]') B(AllParams);

–Y-only dobs for Table B

SELECT ent.value('@id','int') AS Entity_Id
      ,ent.value('@version','nvarchar(max)') AS Entity_Version
      ,JustY.query('.')
FROM @xml.nodes('/xml/entities/entity') A(ent)
OUTER APPLY A.ent.nodes('dobs/dob[@Y and empty(@M)]') C(JustY);

Both queries use a XQuery predicate in .nodes(). The first query will return <dob> nodes where there is a D-, a M- and an Y-attribute. The second query will return <dob> nodes, where the M-attribute is empty (=> not existing).

It’s easy to collect the values for your insert. Simply use .value() instead of .query() and ask either for an attribute by using e.g. @Y or ask for the node’s content by using text():

--complete dobs for Table A
SELECT ent.value('@id','int') AS Entity_Id
      ,ent.value('@version','nvarchar(max)') AS Entity_Version
      ,AllParams.value('@D','int') TheDay
      ,AllParams.value('@M','int') TheMonth
      ,AllParams.value('@Y','int') TheYear
      ,AllParams.value('text()[1]','nvarchar(max)') TheContent
FROM @xml.nodes('/xml/entities/entity') A(ent)
OUTER APPLY A.ent.nodes('dobs/dob[@D and @M and @Y]') B(AllParams);

Hint: Within XML any date/time value should be ISO8601 encoded (e.g. 1970-10-17). Otherwise you are open to culture mix-ups… Furthermore: With ISO8601 you could use date as data type in .value() in order to retrieve the value typesafe. Without this, you are prone to casting errors…