Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

convert xml into csv Expand / Collapse
Author
Message
Posted Friday, April 26, 2013 2:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 11:52 AM
Points: 126, Visits: 524
I'm looking for an easy way to either convert xml to csv. I've tried using the SSIS xml source package way and I'm getting all kinds of truncation errors.

Thanks in advance.
Post #1447152
Posted Friday, April 26, 2013 5:52 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:21 PM
Points: 1,780, Visits: 5,644
Here is a generic example.

You could adjust it for your needs.

declare @x xml;

--== Grab some sample xml ==--

set @x = (select * from sysprocesses for xml auto,elements,type,binary base64);

--== Get a list of headers for the CSV ==--

-- The assumption has been made that we want each top level node as one row in the CSV
-- If this is not correct, modify the FROM clause accordingly
select
stuff(
convert(varchar(max),
nd.query('
for $n in *
return concat(",""",local-name($n),"""")
')
),1,1,''
) as data
from @x.nodes('(*)[1]') as x(nd)

--== The UNION ALL is optional, but it makes sense to me to have the headers with the data ==--
-- this does that
union all

--== Now list the data as CSV for each top level node ==--
-- same assumption as above for the header, adjust as required

select
stuff(
convert(varchar(max),
nd.query('
for $n in *
return
concat(",""",string($n),"""")
')
),1,1,''
) as data
from @x.nodes('*') as x(nd);


Which produces output like this:

"spid" ,"kpid" ,"blocked" ,"waittype" ,"waittime" ,"lastwaittype" ,"waitresource" ,"dbid" ,"uid" ,"cpu" ,"physical_io" ,"memusage" ,"login_time" ,"last_batch" ,"ecid" ,"open_tran" ,"status" ,"sid" ,"hostname" ,"program_name" ,"hostprocess" ,"cmd" ,"nt_domain" ,"nt_username" ,"net_address" ,"net_library" ,"loginame" ,"context_info" ,"sql_handle" ,"stmt_start" ,"stmt_end" ,"request_id"
"1" ,"2412" ,"0" ,"AIM=" ,"49" ,"LOGMGR_QUEUE " ," " ,"0" ,"1" ,"78" ,"0" ,"0" ,"2013-04-25T09:30:24.007" ,"2013-04-25T09:30:24.007" ,"0" ,"0" ,"background " ,"AQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ," " ," " ," " ,"LOG WRITER " ," " ," " ," " ," " ,"sa " ,"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"AAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"0" ,"0" ,"0"
"2" ,"3120" ,"0" ,"AKE=" ,"141055089" ,"KSOURCE_WAKEUP " ," " ,"1" ,"1" ,"0" ,"0" ,"5" ,"2013-04-25T09:30:24.027" ,"2013-04-25T09:30:24.027" ,"0" ,"0" ,"background " ,"AQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ," " ," " ," " ,"SIGNAL HANDLER " ," " ," " ," " ," " ,"sa " ,"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"AAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"0" ,"0" ,"0"
"3" ,"3124" ,"0" ,"Axw=" ,"25" ,"DIRTY_PAGE_POLL " ," " ,"0" ,"1" ,"46" ,"0" ,"0" ,"2013-04-25T09:30:24.030" ,"2013-04-25T09:30:24.030" ,"0" ,"0" ,"background " ,"AQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ," " ," " ," " ,"RECOVERY WRITER " ," " ," " ," " ," " ,"sa " ,"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"AAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"0" ,"0" ,"0"
"4" ,"2500" ,"0" ,"AIQ=" ,"3079" ,"REQUEST_FOR_DEADLOCK_SEARCH " ," " ,"0" ,"1" ,"15" ,"0" ,"0" ,"2013-04-25T09:30:24.030" ,"2013-04-25T09:30:24.030" ,"0" ,"0" ,"background " ,"AQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ," " ," " ," " ,"LOCK MONITOR " ," " ," " ," " ," " ,"sa " ,"AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"AAAAAAAAAAAAAAAAAAAAAAAAAAA=" ,"0" ,"0" ,"0"


This is not production code, just something I banged together, there are known issues, such as it not handling double quotes or commas in the data, but it's kind of interesting to see how easy it can be.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1447204
    Posted Friday, April 26, 2013 8:07 PM
    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Sunday, September 29, 2013 1:24 AM
    Points: 429, Visits: 1,721
    Here's an XML parsing procedure I use all the time. It shreds the XML into a table. Below that I modified the output of the second version to produce a CSV.


    CREATE PROCEDURE dbo.ParseXML

    @strXML AS XML
    ,@rootnode NVARCHAR(255)

    AS
    BEGIN

    /*

    EXEC dbo.ParseXML
    '<AccountDetailsRsp AccountNum="1" AccountStatus="AccountStatus1">
    <PlayerInfo PlayerID="1" FirstName="FirstName1" LastName="LastName1">
    <AddressList>
    <PlayerAddress>
    <Address AddressType="primary" City="City1" State="State1" Zip="Zip1"/>
    <FutureUse>Example Text1</FutureUse>
    <Phone PhoneNumber="PhoneNumber1" PhoneType="Type1" />
    <Phone PhoneNumber="PhoneNumber2" PhoneType="Type2" />
    </PlayerAddress>
    <PlayerAddress>
    <Address AddressType="billing" City="City1" State="State1" Zip="Zip1"/>
    <FutureUse>Example Text2</FutureUse>
    <Phone PhoneNumber="PhoneNumber1" PhoneType="Type1" />
    <Phone PhoneNumber="PhoneNumber2" PhoneType="Type2" />
    </PlayerAddress>
    </AddressList>
    </PlayerInfo>
    </AccountDetailsRsp>'
    ,'AccountDetailsRsp'


    EXEC dbo.ParseXML
    '<items>
    <item id="0001" type="Donut">
    <name>Cake</name>
    <ppu>0.55</ppu>
    <batter id="1001">Regular</batter>
    <batter id="1002">Chocolate</batter>
    <batter id="1003">Blueberry</batter>
    <topping id="5001">None</topping>
    <topping id="5002">Glazed</topping>
    <topping id="5005">Sugar</topping>
    <topping id="5006">Sprinkles</topping>
    <topping id="5003">Chocolate</topping>
    <topping id="5004">Maple</topping>
    </item>
    </items>'
    ,'items'

    */

    SET NOCOUNT ON

    DECLARE
    @strText AS NVARCHAR(MAX)
    ,@idoc INT
    ,@id INT
    ,@parentid INT

    IF OBJECT_ID('tempdb..#ChildList') IS NOT NULL
    DROP TABLE #ChildList

    CREATE TABLE #ChildList (
    [RowNum] INT IDENTITY(1,1) NOT NULL,
    [parentid] INT NULL,
    [id] INT NULL,
    PRIMARY KEY (RowNum),
    UNIQUE (RowNum))

    IF OBJECT_ID('tempdb..#NodeList') IS NOT NULL
    DROP TABLE #NodeList

    CREATE TABLE #NodeList (
    [RowNum] INT NOT NULL,
    [id] INT NULL,
    [parentid] INT NULL,
    [nodetype] INT NULL,
    [localname] NVARCHAR(MAX) NULL,
    [text] NVARCHAR(MAX) NULL,
    PRIMARY KEY (RowNum),
    UNIQUE (RowNum))

    SET @id = 1
    SET @parentid = NULL


    /* Get rid of tabs and extra spaces */

    SET @strText = CAST(@strXML AS NVARCHAR(MAX))

    SET @strText =
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    @strText
    ,' ',' '+CHAR(7))
    ,CHAR(7)+' ','')
    ,CHAR(7),'')
    ,CHAR(9),' ')

    SET @strXML = CONVERT(XML,@strText)


    /* Validate the XML */

    EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML


    /* Parse the XML data */

    ;WITH cte
    AS (
    SELECT
    CAST(p1.parentid AS INT) AS parentid
    ,CAST(p1.id AS INT) AS id
    FROM
    OPENXML (@idoc,@rootnode,2) AS p1

    UNION ALL

    SELECT
    CAST(p2.parentid AS INT) AS parentid
    ,CAST(p2.id AS INT) AS id
    FROM
    OPENXML (@idoc,@rootnode,2) AS p2
    JOIN
    cte
    ON CAST(cte.id AS INT) = CAST(p2.ParentID AS INT)
    WHERE
    CAST(p2.parentid AS INT) = @parentid
    )
    INSERT INTO #ChildList
    SELECT *
    FROM cte

    INSERT INTO #NodeList
    SELECT
    #ChildList.RowNum
    ,xmllist.id
    ,xmllist.parentid
    ,xmllist.nodetype
    ,xmllist.localname
    ,CAST(xmllist.[text] AS NVARCHAR(MAX)) AS [text]
    FROM #ChildList
    INNER JOIN
    OPENXML (@idoc,@rootnode,2) AS xmllist
    ON #ChildList.id = xmllist.id
    WHERE
    #ChildList.RowNum > 0


    /* Display the results */

    ;WITH RecursiveNodes(RowNum,id,parentid,nodepath,localname,[text],nodetype)
    AS (
    SELECT
    #NodeList.RowNum
    ,#NodeList.id
    ,#NodeList.parentid
    ,CAST('/' + REPLACE(REPLACE(REPLACE(REPLACE(#NodeList.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath
    ,#NodeList.localname
    ,CAST(#NodeList.[text] AS NVARCHAR(MAX)) AS [text]
    ,0 AS nodetype
    FROM #ChildList
    INNER JOIN
    #NodeList
    ON #ChildList.id = #NodeList.id
    WHERE
    #NodeList.parentid IS NULL
    AND #ChildList.RowNum > 0
    AND #NodeList.RowNum > 0

    UNION ALL

    SELECT
    n.RowNum
    ,n.id
    ,n.parentid
    ,CAST(r.nodepath + '/'+ REPLACE(REPLACE(REPLACE(REPLACE(n.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath
    ,n.localname
    ,n.[text]
    ,n.nodetype
    FROM #NodeList AS n
    INNER JOIN
    RecursiveNodes AS r
    ON n.parentid = r.id
    WHERE
    n.RowNum > 0
    AND r.RowNum > 0
    AND n.parentid >= 0
    )
    SELECT
    ROW_NUMBER() OVER (ORDER BY Result.RowNum) AS RowNum
    ,Result.id
    ,Result.parentid
    ,Result.nodepath
    ,Result.nodetype
    ,Result.nodename
    ,Result.property
    ,Result.value
    ,Result.nodecontents
    FROM
    (
    SELECT
    rn.RowNum
    ,rn.id
    ,rn.parentid
    ,rn.nodepath
    ,(CASE
    WHEN rn.nodetype = 0 THEN 'Root'
    WHEN rn.nodetype = 1 THEN 'Node'
    WHEN rn.nodetype = 2 THEN 'Property'
    ELSE 'Data'
    END) AS nodetype
    ,(CASE
    WHEN rn.nodetype = 0 THEN rn.localname
    WHEN rn.nodetype = 1 THEN rn.localname
    WHEN rn.nodetype = 2 THEN (SELECT TOP(1) localname FROM RecursiveNodes WHERE id = rn.parentid)
    ELSE NULL
    END) AS nodename
    ,(CASE
    WHEN rn.nodetype = 2 THEN rn.localname
    ELSE NULL
    END) AS property
    ,(CASE
    WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id)
    ELSE NULL
    END) AS value
    ,(CASE
    WHEN rn.nodetype = 1 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id)
    WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.parentid and [text] is not null)
    ELSE NULL
    END) AS nodecontents
    FROM
    RecursiveNodes AS rn
    WHERE
    rn.localname <> '#text'
    ) AS Result
    WHERE
    Result.id >= 0
    AND (Result.id = 0
    OR property IS NOT NULL
    OR value IS NOT NULL
    OR nodecontents IS NOT NULL)

    END


    Same procedure except for the output which will produce a CSV table.


    CREATE PROCEDURE dbo.ParseXMLtoCSV

    @strXML AS XML
    ,@rootnode NVARCHAR(255)

    AS
    BEGIN

    /*

    EXEC dbo.ParseXMLtoCSV
    '<AccountDetailsRsp AccountNum="1" AccountStatus="AccountStatus1">
    <PlayerInfo PlayerID="1" FirstName="FirstName1" LastName="LastName1">
    <AddressList>
    <PlayerAddress>
    <Address AddressType="primary" City="City1" State="State1" Zip="Zip1"/>
    <FutureUse>Example Text1</FutureUse>
    <Phone PhoneNumber="PhoneNumber1" PhoneType="Type1" />
    <Phone PhoneNumber="PhoneNumber2" PhoneType="Type2" />
    </PlayerAddress>
    <PlayerAddress>
    <Address AddressType="billing" City="City1" State="State1" Zip="Zip1"/>
    <FutureUse>Example Text2</FutureUse>
    <Phone PhoneNumber="PhoneNumber1" PhoneType="Type1" />
    <Phone PhoneNumber="PhoneNumber2" PhoneType="Type2" />
    </PlayerAddress>
    </AddressList>
    </PlayerInfo>
    </AccountDetailsRsp>'
    ,'AccountDetailsRsp'


    EXEC dbo.ParseXMLtoCSV
    '<items>
    <item id="0001" type="Donut">
    <name>Cake</name>
    <ppu>0.55</ppu>
    <batter id="1001">Regular</batter>
    <batter id="1002">Chocolate</batter>
    <batter id="1003">Blueberry</batter>
    <topping id="5001">None</topping>
    <topping id="5002">Glazed</topping>
    <topping id="5005">Sugar</topping>
    <topping id="5006">Sprinkles</topping>
    <topping id="5003">Chocolate</topping>
    <topping id="5004">Maple</topping>
    </item>
    </items>'
    ,'items'

    */

    SET NOCOUNT ON

    DECLARE
    @strText AS NVARCHAR(MAX)
    ,@idoc INT
    ,@id INT
    ,@parentid INT

    IF OBJECT_ID('tempdb..#ChildList') IS NOT NULL
    DROP TABLE #ChildList

    CREATE TABLE #ChildList (
    [RowNum] INT IDENTITY(1,1) NOT NULL,
    [parentid] INT NULL,
    [id] INT NULL,
    PRIMARY KEY (RowNum),
    UNIQUE (RowNum))

    IF OBJECT_ID('tempdb..#NodeList') IS NOT NULL
    DROP TABLE #NodeList

    CREATE TABLE #NodeList (
    [RowNum] INT NOT NULL,
    [id] INT NULL,
    [parentid] INT NULL,
    [nodetype] INT NULL,
    [localname] NVARCHAR(MAX) NULL,
    [text] NVARCHAR(MAX) NULL,
    PRIMARY KEY (RowNum),
    UNIQUE (RowNum))

    SET @id = 1
    SET @parentid = NULL


    /* Get rid of tabs and extra spaces */

    SET @strText = CAST(@strXML AS NVARCHAR(MAX))

    SET @strText =
    REPLACE(
    REPLACE(
    REPLACE(
    REPLACE(
    @strText
    ,' ',' '+CHAR(7))
    ,CHAR(7)+' ','')
    ,CHAR(7),'')
    ,CHAR(9),' ')

    SET @strXML = CONVERT(XML,@strText)


    /* Validate the XML */

    EXEC sp_xml_preparedocument @idoc OUTPUT, @strXML


    /* Parse the XML data */

    ;WITH cte
    AS (
    SELECT
    CAST(p1.parentid AS INT) AS parentid
    ,CAST(p1.id AS INT) AS id
    FROM
    OPENXML (@idoc,@rootnode,2) AS p1

    UNION ALL

    SELECT
    CAST(p2.parentid AS INT) AS parentid
    ,CAST(p2.id AS INT) AS id
    FROM
    OPENXML (@idoc,@rootnode,2) AS p2
    JOIN
    cte
    ON CAST(cte.id AS INT) = CAST(p2.ParentID AS INT)
    WHERE
    CAST(p2.parentid AS INT) = @parentid
    )
    INSERT INTO #ChildList
    SELECT *
    FROM cte

    INSERT INTO #NodeList
    SELECT
    #ChildList.RowNum
    ,xmllist.id
    ,xmllist.parentid
    ,xmllist.nodetype
    ,xmllist.localname
    ,CAST(xmllist.[text] AS NVARCHAR(MAX)) AS [text]
    FROM #ChildList
    INNER JOIN
    OPENXML (@idoc,@rootnode,2) AS xmllist
    ON #ChildList.id = xmllist.id
    WHERE
    #ChildList.RowNum > 0


    /* Display the results */

    ;WITH RecursiveNodes(RowNum,id,parentid,nodepath,localname,[text],nodetype)
    AS (
    SELECT
    #NodeList.RowNum
    ,#NodeList.id
    ,#NodeList.parentid
    ,CAST('/' + REPLACE(REPLACE(REPLACE(REPLACE(#NodeList.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath
    ,#NodeList.localname
    ,CAST(#NodeList.[text] AS NVARCHAR(MAX)) AS [text]
    ,0 AS nodetype
    FROM #ChildList
    INNER JOIN
    #NodeList
    ON #ChildList.id = #NodeList.id
    WHERE
    #NodeList.parentid IS NULL
    AND #ChildList.RowNum > 0
    AND #NodeList.RowNum > 0

    UNION ALL

    SELECT
    n.RowNum
    ,n.id
    ,n.parentid
    ,CAST(r.nodepath + '/'+ REPLACE(REPLACE(REPLACE(REPLACE(n.localname,'&',''),'?',''),' ',''),'.','') AS NVARCHAR(255)) AS nodepath
    ,n.localname
    ,n.[text]
    ,n.nodetype
    FROM #NodeList AS n
    INNER JOIN
    RecursiveNodes AS r
    ON n.parentid = r.id
    WHERE
    n.RowNum > 0
    AND r.RowNum > 0
    AND n.parentid >= 0
    )
    SELECT
    '"id","parentid","nodepath","nodetype","nodename","property","value","nodecontents"'
    AS XMLtoCSV
    UNION ALL
    SELECT
    STUFF(
    (SELECT
    ',"'+CAST(ISNULL(Result.id,'') AS VARCHAR(50))
    +'","'+CAST(ISNULL(Result.parentid,'') AS VARCHAR(50))
    +'","'+CAST(ISNULL(Result.nodepath,'') AS VARCHAR(50))
    +'","'+CAST(ISNULL(Result.nodetype,'') AS VARCHAR(50))
    +'","'+CAST(ISNULL(Result.nodename,'') AS VARCHAR(50))
    +'","'+CAST(ISNULL(Result.property,'') AS VARCHAR(50))
    +'","'+CAST(ISNULL(Result.value,'') AS VARCHAR(50))
    +'","'+CAST(ISNULL(Result.nodecontents,'') AS VARCHAR(50))
    FOR XML PATH(''))
    ,1,1,'') AS XMLtoCSV
    FROM
    (
    SELECT
    rn.id
    ,rn.parentid
    ,rn.nodepath
    ,(CASE
    WHEN rn.nodetype = 0 THEN 'Root'
    WHEN rn.nodetype = 1 THEN 'Node'
    WHEN rn.nodetype = 2 THEN 'Property'
    ELSE 'Data'
    END) AS nodetype
    ,(CASE
    WHEN rn.nodetype = 0 THEN rn.localname
    WHEN rn.nodetype = 1 THEN rn.localname
    WHEN rn.nodetype = 2 THEN (SELECT TOP(1) localname FROM RecursiveNodes WHERE id = rn.parentid)
    ELSE NULL
    END) AS nodename
    ,(CASE
    WHEN rn.nodetype = 2 THEN rn.localname
    ELSE NULL
    END) AS property
    ,(CASE
    WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id)
    ELSE NULL
    END) AS value
    ,(CASE
    WHEN rn.nodetype = 1 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.id)
    WHEN rn.nodetype = 2 THEN (SELECT TOP(1) [text] FROM RecursiveNodes WHERE parentid = rn.parentid and [text] is not null)
    ELSE NULL
    END) AS nodecontents
    FROM
    RecursiveNodes AS rn
    WHERE
    rn.localname <> '#text'
    ) AS Result
    WHERE
    Result.id >= 0
    AND (Result.id = 0
    OR property IS NOT NULL
    OR value IS NOT NULL
    OR nodecontents IS NOT NULL)

    END


    Post #1447213
    Posted Saturday, April 27, 2013 4:57 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Wednesday, July 9, 2014 11:52 AM
    Points: 126, Visits: 524
    Thanks!
    Post #1447249
    Posted Saturday, April 27, 2013 4:58 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Wednesday, July 9, 2014 11:52 AM
    Points: 126, Visits: 524
    Thanks
    Post #1447250
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse