2

This is a follow up question to T-Sql xml query with namespace

I have the following XML data.I need to convert it into a SQL table.I used the following SQL query:

 declare @XML xml =
    '<?xml version="1.0" encoding="utf-8"?>
    <RS xmlns="urn:schemas-microsoft-com:xml-analysis:rowset">
        <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:saw-sql="urn:saw-sql" targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset">
            <xsd:complexType name="R">
                <xsd:sequence>
                    <xsd:element name="C0" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Product Binary&quot;.&quot;Binary Name&quot;" saw-sql:displayFormula="&quot;Product Binary&quot;.&quot;Binary Name&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Product Binary" saw-sql:columnHeading="Binary Name" saw-sql:columnID="ca5ff07f42dfb3ea1"/>
                    <xsd:element name="C1" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Product Binary&quot;.&quot;Download Display Name&quot;" saw-sql:displayFormula="&quot;Product Binary&quot;.&quot;Download Display Name&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Product Binary" saw-sql:columnHeading="Download Display Name" saw-sql:columnID="c1f02dadb0dfa49a0"/>
                    <xsd:element name="C2" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Download Start Date&quot;.&quot;Quarter&quot;" saw-sql:displayFormula="&quot;Download Start Date&quot;.&quot;Quarter&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Download Start Date" saw-sql:columnHeading="Quarter" saw-sql:columnID="c66bc4e116b33b4ef"/>
                    <xsd:element name="C3" type="xsd:date" minOccurs="1" maxOccurs="1" saw-sql:type="date" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Download Start Date&quot;.&quot;Cal Date&quot;" saw-sql:displayFormula="&quot;Download Start Date&quot;.&quot;Cal Date&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Download Start Date" saw-sql:columnHeading="Cal Date" saw-sql:columnID="c5bb40fa14f988e4a"/>
                    <xsd:element name="C4" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Download Start Date&quot;.&quot;Week&quot;" saw-sql:displayFormula="&quot;Download Start Date&quot;.&quot;Week&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Download Start Date" saw-sql:columnHeading="Week" saw-sql:columnID="ce8ad46904662e816"/>
                    <xsd:element name="C5" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Download Start Date&quot;.&quot;Month&quot;" saw-sql:displayFormula="&quot;Download Start Date&quot;.&quot;Month&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Download Start Date" saw-sql:columnHeading="Month" saw-sql:columnID="ca19e13040145f84b"/>
                    <xsd:element name="C6" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Download Start Date&quot;.&quot;Year&quot;" saw-sql:displayFormula="&quot;Download Start Date&quot;.&quot;Year&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Download Start Date" saw-sql:columnHeading="Year" saw-sql:columnID="cd55ae47d83c209a6"/>
                    <xsd:element name="C7" type="xsd:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Customer Segment&quot;.&quot;Customer Segment&quot;" saw-sql:displayFormula="&quot;Customer Segment&quot;.&quot;Customer Segment&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Customer Segment" saw-sql:columnHeading="Customer Segment" saw-sql:columnID="cebaa8810a3cb8064"/>
                    <xsd:element name="C8" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Customer Contact&quot;.&quot;Customer Type&quot;" saw-sql:displayFormula="&quot;Customer Contact&quot;.&quot;Customer Type&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Customer Contact" saw-sql:columnHeading="Customer Type" saw-sql:columnID="c602557f90d4ad9da"/>
                    <xsd:element name="C9" type="xsd:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Dnb Organization&quot;.&quot;Business Name&quot;" saw-sql:displayFormula="&quot;Dnb Organization&quot;.&quot;Business Name&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Dnb Organization" saw-sql:columnHeading="Business Name" saw-sql:columnID="c5b35569f06cec8ea"/>
                    <xsd:element name="C10" type="xsd:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Dnb Organization&quot;.&quot;Country Region&quot;" saw-sql:displayFormula="&quot;Dnb Organization&quot;.&quot;Country Region&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Dnb Organization" saw-sql:columnHeading="Country Region" saw-sql:columnID="c522eb1b6f5c0fe4d"/>
                    <xsd:element name="C11" type="xsd:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Dnb Organization&quot;.&quot;Country Geo&quot;" saw-sql:displayFormula="&quot;Dnb Organization&quot;.&quot;Country Geo&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Dnb Organization" saw-sql:columnHeading="Country Geo" saw-sql:columnID="c1874f494099b9641"/>
                    <xsd:element name="C12" type="xsd:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Dnb Organization&quot;.&quot;Iso Country Name&quot;" saw-sql:displayFormula="&quot;Dnb Organization&quot;.&quot;Iso Country Name&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Dnb Organization" saw-sql:columnHeading="Iso Country Name" saw-sql:columnID="ceb5291bac5a83a51"/>
                    <xsd:element name="C13" type="xsd:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Customer Contact&quot;.&quot;City Name&quot;" saw-sql:displayFormula="&quot;Customer Contact&quot;.&quot;City Name&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Customer Contact" saw-sql:columnHeading="City Name" saw-sql:columnID="c3fcf2b0a26f2d882"/>
                    <xsd:element name="C14" type="xsd:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Customer Contact&quot;.&quot;Country&quot;" saw-sql:displayFormula="&quot;Customer Contact&quot;.&quot;Country&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Customer Contact" saw-sql:columnHeading="Country" saw-sql:columnID="c62a616664e32a709"/>
                    <xsd:element name="C15" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Customer Contact&quot;.&quot;Last Name&quot;" saw-sql:displayFormula="&quot;Customer Contact&quot;.&quot;Last Name&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Customer Contact" saw-sql:columnHeading="Last Name" saw-sql:columnID="c0651d86673c4e74d"/>
                    <xsd:element name="C16" type="xsd:string" minOccurs="0" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Customer Contact&quot;.&quot;First Name&quot;" saw-sql:displayFormula="&quot;Customer Contact&quot;.&quot;First Name&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Customer Contact" saw-sql:columnHeading="First Name" saw-sql:columnID="c9dfc11cb810a623d"/>
                    <xsd:element name="C17" type="xsd:string" minOccurs="1" maxOccurs="1" saw-sql:type="varchar" saw-sql:sqlFormula="&quot;CBR&quot;.&quot;Customer Contact&quot;.&quot;Email Address&quot;" saw-sql:displayFormula="&quot;Customer Contact&quot;.&quot;Email Address&quot;" saw-sql:aggregationRule="none" saw-sql:aggregationType="nonAgg" saw-sql:tableHeading="Customer Contact" saw-sql:columnHeading="Email Address" saw-sql:columnID="cf7152d03553d4987"/>
                </xsd:sequence>
            </xsd:complexType>
        </xsd:schema>
        <R>
            <C0>/software/vcm/56/VCM-OS-Provisioning-Server-5.6.iso</C0>
            <C1>VMware vCenter Configuration Manager 5.6.0</C1>
            <C2>14Q1</C2>
            <C3>2014-01-02</C3>
            <C4>W01</C4>
            <C5>JANUARY</C5>
            <C6>2014</C6>
            <C7>Enterprise</C7>
            <C8>ESTABLISHED</C8>
            <C9>XCHANGING PLC</C9>
            <C10>NORTHERN EUROPE</C10>
            <C11>EMEA</C11>
            <C12>UNITED KINGDOM</C12>
            <C13>BASILDON</C13>
            <C14>GB</C14>
            <C15>Delves</C15>
            <C16>Craig</C16>
            <C17>[email protected]</C17>
        </R></RS>'

    declare @SQL nvarchar(max) = ''
    declare @Col nvarchar(max) = ', T.N.value(''[COLNAME][1]'', ''varchar(100)'') as [COLNAME]' 

    ;WITH XMLNAMESPACES( DEFAULT 'urn:schemas-microsoft-com:xml-analysis:rowset')
    select @SQL = @SQL + replace(@Col, '[COLNAME]', T.N.value('local-name(.)', 'sysname'))
    from @XML.nodes('//R') as T(N)

    set @SQL = 'select '+stuff(@SQL, 1, 2, '')+' from @XML.nodes(''//R'') as T(N)' 

    exec sp_executesql @SQL, N'@XML xml', @XML

When I run the query,it returns nothing.Is there a problem with declaring the namespace.Looking for the solution.

Improve this question
0

1 Answer 1

Reset to default
1

You're building a dynamic SQL statement, but the with xmlnamespaces part does not make it into the dynamic SQL query.

Note that XPath is case sensitive, so use //R instead of //r:

; with  xmlnamespaces(default 'urn:schemas-microsoft-com:xml-analysis:rowset')
select  t.n.value('C0[1]', 'varchar(100)') as r 
from    @xml.nodes('//R') as t(n)

This prints:

/software/vcm/56/VCM-OS-Provisioning-Server-5.6.iso

To find a list of all columns, you could:

; with  xmlnamespaces(default 'urn:schemas-microsoft-com:xml-analysis:rowset')
select  T.N.value('local-name(.)', 'sysname')
from    @XML.nodes('//R/child::*') as T(N)

This prints:

C0
C1
C2
C3
...
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.