Loading generated XML to a table

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)
LVL 1
AlyantoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
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
ZberteocCommented:
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
ZberteocCommented:
From variable you can insert into a table easily. You cant insert directly from query if you use FOR XML.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

AlyantoAuthor Commented:
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
ZberteocCommented:
Ok, instead of set use select:

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

Open in new window

0
AlyantoAuthor Commented:
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
AlyantoAuthor Commented:
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
ZberteocCommented:
Can you post the query you tried here?
0
Anthony PerkinsCommented:
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
Anthony PerkinsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AlyantoAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.