Solved

Loading generated XML to a table

Posted on 2014-02-27
11
290 Views
Last Modified: 2014-03-03
I have the query below which produces the XML I need.  The problem is I want to insert it into a table or a variable.  How is this best done from SQL server?

SELECT
            1 AS 'Tag'
            ,NULL As 'Parent'
            ,0 AS 'Branches!1!Sort!hide'
            ,NULL AS 'Branches!1!'
            ,NULL AS 'Branch!2!BranchNumber!ID'
            ,NULL AS 'Branch!2!BranchName'
            ,NULL AS 'Branch!2!BranchDistrict'
            ,NULL AS 'Branch!2!BranchRegion'
            ,NULL AS 'Branch!2!BranchZone'
            ,NULL AS 'Branch!2!BranchCountry'
            ,NULL AS 'Branch!2!BranchCountryNo'
            ,NULL AS 'Despatchs!3!ELEMENT'
            ,NULL AS 'Stock!5!ELEMENT'
            ,NULL AS 'Despatch!4!LongCode!ID'
            ,NULL AS 'Despatch!4!Units'
            ,NULL AS 'Despatch!4!Memo'
            ,NULL AS 'Despatch!4!TransactionType'
            ,NULL AS 'Despatch!4!OtherBranchNumber'
            ,NULL AS 'Item!6!LongCode!ID'
            ,NULL AS 'Item!6!ShortCode'
            ,NULL AS 'Item!6!ProductDescription'
            ,NULL AS 'Item!6!BranchSellingPrice'
            ,NULL AS 'Item!6!CurrentStockUnits'
            ,NULL AS 'Item!6!ToPickUnits'
UNION ALL
      SELECT DISTINCT
            2 AS Tag
            ,1 AS Parent
            ,br.BranchNumber * 100
            ,NULL
            ,br.BranchNumber
            ,br.BranchName
            ,br.BranchDistrict
            ,br.BranchRegion
            ,br.BranchZone
            ,br.BranchCountry
            ,br.BranchCountryNo
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber
      WHERE di.BranchNumber = 15
UNION ALL
SELECT DISTINCT
    3 AS Tag
    ,2 AS Parent
    ,BranchNumber * 100 + 1
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
SELECT DISTINCT
    4 AS Tag
    ,3 AS Parent
    ,BranchNumber * 100 + 2
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,LongCode
    ,Units
    ,TransactionType
    ,Memo
    ,OtherBranchNumber
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
      SELECT DISTINCT
            5 AS Tag
            ,2 AS Parent
            ,BranchNumber * 100
            ,NULL
            ,BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
      UNION ALL
      SELECT DISTINCT
            6 AS Tag
            ,5 AS Parent
            ,di.BranchNumber * 100
            ,NULL
            ,di.BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,br.LongCode
            ,br.ShortCode
            ,br.ProductDescription
            ,br.BranchSellingPrice
            ,br.CurrentStockUnits
            ,br.ToPickUnits
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber
      WHERE di.BranchNumber = 15
ORDER BY [Branches!1!Sort!hide]
FOR XML EXPLICIT)
0
Comment
Question by:Alyanto
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39892224
declare
	@xml_string varchar(max)
SET
	@xml_string=(
SELECT
            1 AS 'Tag'
            ,NULL As 'Parent'
            ,0 AS 'Branches!1!Sort!hide'
            ,NULL AS 'Branches!1!'
            ,NULL AS 'Branch!2!BranchNumber!ID'
            ,NULL AS 'Branch!2!BranchName'
            ,NULL AS 'Branch!2!BranchDistrict'
            ,NULL AS 'Branch!2!BranchRegion'
            ,NULL AS 'Branch!2!BranchZone'
            ,NULL AS 'Branch!2!BranchCountry'
            ,NULL AS 'Branch!2!BranchCountryNo'
            ,NULL AS 'Despatchs!3!ELEMENT'
            ,NULL AS 'Stock!5!ELEMENT'
            ,NULL AS 'Despatch!4!LongCode!ID'
            ,NULL AS 'Despatch!4!Units'
            ,NULL AS 'Despatch!4!Memo'
            ,NULL AS 'Despatch!4!TransactionType'
            ,NULL AS 'Despatch!4!OtherBranchNumber'
            ,NULL AS 'Item!6!LongCode!ID'
            ,NULL AS 'Item!6!ShortCode'
            ,NULL AS 'Item!6!ProductDescription'
            ,NULL AS 'Item!6!BranchSellingPrice'
            ,NULL AS 'Item!6!CurrentStockUnits'
            ,NULL AS 'Item!6!ToPickUnits'
UNION ALL
      SELECT DISTINCT
            2 AS Tag
            ,1 AS Parent
            ,br.BranchNumber * 100
            ,NULL 
            ,br.BranchNumber
            ,br.BranchName 
            ,br.BranchDistrict
            ,br.BranchRegion
            ,br.BranchZone 
            ,br.BranchCountry 
            ,br.BranchCountryNo 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber 
      WHERE di.BranchNumber = 15
UNION ALL
SELECT DISTINCT
    3 AS Tag
    ,2 AS Parent
    ,BranchNumber * 100 + 1
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
SELECT DISTINCT
    4 AS Tag
    ,3 AS Parent
    ,BranchNumber * 100 + 2
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,LongCode
    ,Units
    ,TransactionType
    ,Memo
    ,OtherBranchNumber
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
      SELECT DISTINCT
            5 AS Tag
            ,2 AS Parent
            ,BranchNumber * 100
            ,NULL
            ,BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
      UNION ALL
      SELECT DISTINCT
            6 AS Tag
            ,5 AS Parent
            ,di.BranchNumber * 100
            ,NULL
            ,di.BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,br.LongCode
            ,br.ShortCode
            ,br.ProductDescription 
            ,br.BranchSellingPrice 
            ,br.CurrentStockUnits
            ,br.ToPickUnits 
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber 
      WHERE di.BranchNumber = 15
ORDER BY [Branches!1!Sort!hide]
FOR XML EXPLICIT)

SELECT @xml_string

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39892236
To insert into an XML variable:
declare
	@xml xml
SET
	@xml=CONVERT(xml, (
SELECT
            1 AS 'Tag'
            ,NULL As 'Parent'
            ,0 AS 'Branches!1!Sort!hide'
            ,NULL AS 'Branches!1!'
            ,NULL AS 'Branch!2!BranchNumber!ID'
            ,NULL AS 'Branch!2!BranchName'
            ,NULL AS 'Branch!2!BranchDistrict'
            ,NULL AS 'Branch!2!BranchRegion'
            ,NULL AS 'Branch!2!BranchZone'
            ,NULL AS 'Branch!2!BranchCountry'
            ,NULL AS 'Branch!2!BranchCountryNo'
            ,NULL AS 'Despatchs!3!ELEMENT'
            ,NULL AS 'Stock!5!ELEMENT'
            ,NULL AS 'Despatch!4!LongCode!ID'
            ,NULL AS 'Despatch!4!Units'
            ,NULL AS 'Despatch!4!Memo'
            ,NULL AS 'Despatch!4!TransactionType'
            ,NULL AS 'Despatch!4!OtherBranchNumber'
            ,NULL AS 'Item!6!LongCode!ID'
            ,NULL AS 'Item!6!ShortCode'
            ,NULL AS 'Item!6!ProductDescription'
            ,NULL AS 'Item!6!BranchSellingPrice'
            ,NULL AS 'Item!6!CurrentStockUnits'
            ,NULL AS 'Item!6!ToPickUnits'
UNION ALL
      SELECT DISTINCT
            2 AS Tag
            ,1 AS Parent
            ,br.BranchNumber * 100
            ,NULL 
            ,br.BranchNumber
            ,br.BranchName 
            ,br.BranchDistrict
            ,br.BranchRegion
            ,br.BranchZone 
            ,br.BranchCountry 
            ,br.BranchCountryNo 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber 
      WHERE di.BranchNumber = 15
UNION ALL
SELECT DISTINCT
    3 AS Tag
    ,2 AS Parent
    ,BranchNumber * 100 + 1
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
SELECT DISTINCT
    4 AS Tag
    ,3 AS Parent
    ,BranchNumber * 100 + 2
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,LongCode
    ,Units
    ,TransactionType
    ,Memo
    ,OtherBranchNumber
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
    ,NULL
FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
UNION ALL
      SELECT DISTINCT
            5 AS Tag
            ,2 AS Parent
            ,BranchNumber * 100
            ,NULL
            ,BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
      FROM  dbo.PI_Dispatch WHERE BranchNumber = 15
      UNION ALL
      SELECT DISTINCT
            6 AS Tag
            ,5 AS Parent
            ,di.BranchNumber * 100
            ,NULL
            ,di.BranchNumber AS Trial
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL 
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,NULL
            ,br.LongCode
            ,br.ShortCode
            ,br.ProductDescription 
            ,br.BranchSellingPrice 
            ,br.CurrentStockUnits
            ,br.ToPickUnits 
      FROM  dbo.PI_Dispatch di INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber 
      WHERE di.BranchNumber = 15
ORDER BY [Branches!1!Sort!hide]
FOR XML EXPLICIT))

SELECT @xml

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39892238
From variable you can insert into a table easily. You cant insert directly from query if you use FOR XML.
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 1

Author Comment

by:Alyanto
ID: 39892286
Option one resulted in this message

Msg 1086, Level 15, State 1, Line 166
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.


Option Two Resulted in this message
Msg 1086, Level 15, State 1, Line 166
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.


A little cryptic but I think it means

SELECT * FROM ( SELECT ... FROM ADateSource) AS DAT or similar for each sub query, or am I barking up the wrong tree?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39892310
Ok, instead of set use select:

declare
	@xml_string varchar(max)
SELECT
	@xml_string=
...(

Open in new window

0
 
LVL 1

Author Comment

by:Alyanto
ID: 39892333
My choices seem to be put the data in a table and deal with it from there, export it to a file and deal with it from there or send it to a variable.  I would be able to work this through with most of those options.

I tried the encasing the sub queries this did not work!
0
 
LVL 1

Author Comment

by:Alyanto
ID: 39892344
I tried the third option proposed and this was the result:

Msg 1086, Level 15, State 1, Line 167
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39892379
Can you post the query you tried here?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39893623
The part that you are missing is:
1. FOR XML EXPLICIT, TYPE
2. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39893627
In other words, try it this way:
DECLARE @X xml 

SELECT @X = 
(SELECT *
FROM (
SELECT  1 AS 'Tag',
        NULL AS 'Parent',
        0 AS 'Branches!1!Sort!hide',
        NULL AS 'Branches!1!',
        NULL AS 'Branch!2!BranchNumber!ID',
        NULL AS 'Branch!2!BranchName',
        NULL AS 'Branch!2!BranchDistrict',
        NULL AS 'Branch!2!BranchRegion',
        NULL AS 'Branch!2!BranchZone',
        NULL AS 'Branch!2!BranchCountry',
        NULL AS 'Branch!2!BranchCountryNo',
        NULL AS 'Despatchs!3!ELEMENT',
        NULL AS 'Stock!5!ELEMENT',
        NULL AS 'Despatch!4!LongCode!ID',
        NULL AS 'Despatch!4!Units',
        NULL AS 'Despatch!4!Memo',
        NULL AS 'Despatch!4!TransactionType',
        NULL AS 'Despatch!4!OtherBranchNumber',
        NULL AS 'Item!6!LongCode!ID',
        NULL AS 'Item!6!ShortCode',
        NULL AS 'Item!6!ProductDescription',
        NULL AS 'Item!6!BranchSellingPrice',
        NULL AS 'Item!6!CurrentStockUnits',
        NULL AS 'Item!6!ToPickUnits'
UNION ALL
SELECT DISTINCT
        2 AS Tag,
        1 AS Parent,
        br.BranchNumber * 100,
        NULL,
        br.BranchNumber,
        br.BranchName,
        br.BranchDistrict,
        br.BranchRegion,
        br.BranchZone,
        br.BranchCountry,
        br.BranchCountryNo,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL
FROM    dbo.PI_Dispatch di
        INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber
WHERE   di.BranchNumber = 15
UNION ALL
SELECT DISTINCT
        3 AS Tag,
        2 AS Parent,
        BranchNumber * 100 + 1,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL
FROM    dbo.PI_Dispatch
WHERE   BranchNumber = 15
UNION ALL
SELECT DISTINCT
        4 AS Tag,
        3 AS Parent,
        BranchNumber * 100 + 2,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        LongCode,
        Units,
        TransactionType,
        Memo,
        OtherBranchNumber,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL
FROM    dbo.PI_Dispatch
WHERE   BranchNumber = 15
UNION ALL
SELECT DISTINCT
        5 AS Tag,
        2 AS Parent,
        BranchNumber * 100,
        NULL,
        BranchNumber AS Trial,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL
FROM    dbo.PI_Dispatch
WHERE   BranchNumber = 15
UNION ALL
SELECT DISTINCT
        6 AS Tag,
        5 AS Parent,
        di.BranchNumber * 100,
        NULL,
        di.BranchNumber AS Trial,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        NULL,
        br.LongCode,
        br.ShortCode,
        br.ProductDescription,
        br.BranchSellingPrice,
        br.CurrentStockUnits,
        br.ToPickUnits
FROM    dbo.PI_Dispatch di
        INNER JOIN [dbo].[PI_BranchStock] br ON di.BranchNumber = br.BranchNumber
WHERE   di.BranchNumber = 15
) a ORDER BY [Branches!1!Sort!hide] FOR XML EXPLICIT)

Open in new window

0
 
LVL 1

Author Closing Comment

by:Alyanto
ID: 39899840
Thank you Anthony for your answer this has salvaged a considerable amount of work to date.  I would also like to acknowledge the contribution of the Zberteoc who was clearly going in the right direction.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question