Search code examples
sql-serverxquerycross-apply

XQuery cross apply returns extra records

I have following structure in XML column. It supposed to show stored procedures and list used tables and columns underneath.

It is showing extra records. Please see below structure and queries:

CREATE TABLE #T (
    ID  int primary key, 
    MetaData xml
)

insert into #T values(1,
'<root>
    <object name="myProc">
        <tables>
            <table database="LynxReporting" schema="Secure" name="factPortCore">
                <columns>
                    <column name="SnapshotKey"/>
                    <column name="SnapshotDt"/>
                </columns>
            </table>
            <table database="LynxSnapshot" schema="dbo" name="Loan">
                <columns>
                    <column name="LoanProgId"/>
                    <column name="FinType"/>
                </columns>
            </table>
            <table database="LynxReporting" schema="dbo" name="dimGLSrcSysId">
                <columns>
                    <column name="GLSrcSysId"/>
                </columns>
            </table>
        </tables>
    </object>
    <object name="usp_Recon">
        <tables>
            <table database="LynxReporting" schema="Secure" name="dimAppSysId">
                <columns>
                    <column name="AppSysId"/>
                    <column name="AppSysIdLongDesc"/>
                </columns>
            </table>
        </tables>
    </object>
</root>')

SELECT
    t.x.value('@name', 'varchar(max)') as TableName
    , c.x.value('@name', 'varchar(max)') as ColumnName
FROM #T
    CROSS APPLY MetaData.nodes('root/object/tables/table') as t(x)
    CROSS APPLY MetaData.nodes('root/object/tables/table/columns/column') as c(x)
order by 1,2

What I get (you see the irrelevant rows): 

╔═══════════════╦══════════════════╗
║ TableName     ║ ColumnName       ║
╠═══════════════╬══════════════════╣
║ Loan          ║ AppSysId         ║
╠═══════════════╬══════════════════╣
║ Loan          ║ AppSysIdLongDesc ║
╠═══════════════╬══════════════════╣
║ Loan          ║ FinType          ║
╠═══════════════╬══════════════════╣
║ Loan          ║ GLSrcSysId       ║
╠═══════════════╬══════════════════╣
║ Loan          ║ LoanProgId       ║
╠═══════════════╬══════════════════╣
║ Loan          ║ SnapshotDt       ║
╠═══════════════╬══════════════════╣
║ Loan          ║ SnapshotKey      ║
╠═══════════════╬══════════════════╣
║ dimAppSysId   ║ AppSysId         ║
╠═══════════════╬══════════════════╣
║ dimAppSysId   ║ AppSysIdLongDesc ║
╠═══════════════╬══════════════════╣
║ dimAppSysId   ║ FinType          ║
╠═══════════════╬══════════════════╣
║ dimAppSysId   ║ GLSrcSysId       ║
╠═══════════════╬══════════════════╣
║ dimAppSysId   ║ LoanProgId       ║
╠═══════════════╬══════════════════╣
║ dimAppSysId   ║ SnapshotDt       ║
╠═══════════════╬══════════════════╣
║ dimAppSysId   ║ SnapshotKey      ║
╠═══════════════╬══════════════════╣
║ dimGLSrcSysId ║ AppSysId         ║
╠═══════════════╬══════════════════╣
║ dimGLSrcSysId ║ AppSysIdLongDesc ║
╠═══════════════╬══════════════════╣
║ dimGLSrcSysId ║ FinType          ║
╠═══════════════╬══════════════════╣
║ dimGLSrcSysId ║ GLSrcSysId       ║
╠═══════════════╬══════════════════╣
║ dimGLSrcSysId ║ LoanProgId       ║
╠═══════════════╬══════════════════╣
║ dimGLSrcSysId ║ SnapshotDt       ║
╠═══════════════╬══════════════════╣
║ dimGLSrcSysId ║ SnapshotKey      ║
╠═══════════════╬══════════════════╣
║ factPortCore  ║ AppSysId         ║
╠═══════════════╬══════════════════╣
║ factPortCore  ║ AppSysIdLongDesc ║
╠═══════════════╬══════════════════╣
║ factPortCore  ║ FinType          ║
╠═══════════════╬══════════════════╣
║ factPortCore  ║ GLSrcSysId       ║
╠═══════════════╬══════════════════╣
║ factPortCore  ║ LoanProgId       ║
╠═══════════════╬══════════════════╣
║ factPortCore  ║ SnapshotDt       ║
╠═══════════════╬══════════════════╣
║ factPortCore  ║ SnapshotKey      ║
╚═══════════════╩══════════════════╝

What I want:

╔════════════╦═══════════════╦══════════════════╗
║ ObjectName ║ TableName     ║ ColumnName       ║
╠════════════╬═══════════════╬══════════════════╣
║ myProc     ║ Loan          ║ FinType          ║
╠════════════╬═══════════════╬══════════════════╣
║ myProc     ║ Loan          ║ LoanProgId       ║
╠════════════╬═══════════════╬══════════════════╣
║ usp_Recon  ║ dimAppSysId   ║ AppSysId         ║
╠════════════╬═══════════════╬══════════════════╣
║ usp_Recon  ║ dimAppSysId   ║ AppSysIdLongDesc ║
╠════════════╬═══════════════╬══════════════════╣
║ myProc     ║ dimGLSrcSysId ║ GLSrcSysId       ║
╠════════════╬═══════════════╬══════════════════╣
║ myProc     ║ factPortCore  ║ SnapshotDt       ║
╠════════════╬═══════════════╬══════════════════╣
║ myProc     ║ factPortCore  ║ SnapshotKey      ║
╚════════════╩═══════════════╩══════════════════╝

As you see, I also want to have ObjectName column. I don't know how should I change my query. Any help is appreciated.

Solution

  • cross apply the value from the previous set

    SELECT
    r.o.value('@name', 'varchar(max)') as ObjectName
    , t.x.value('@name', 'varchar(max)') as TableName
    , c.x.value('@name', 'varchar(max)') as ColumnName
FROM #T
    CROSS APPLY MetaData.nodes('root/object') as r(o)
    CROSS APPLY r.o.nodes('tables/table') as t(x)
    CROSS APPLY t.x.nodes('columns/column') as c(x)