Need Help with XML

Question

Need Help with XML

Jampandu

Ten Centuries

Points: 1062
More actions
August 2, 2016 at 12:07 pm

#328537

Hi Experts,
Please help me with following xml, Need to convert sql data to xml.
Here is the sample data
CREATE TABLE [#XMLTable](
[BatchID] [nvarchar](25) NOT NULL,
[Count] [int] NOT NULL,
[Status] [varchar](6) NOT NULL,
[Type] [varchar](10) NOT NULL,
[Code] [varchar](10) NOT NULL,
[Number] [varchar](100) NOT NULL,
[ID] [int] NOT NULL,
[Error] [int] NOT NULL,
[Address] [nvarchar](1023) NOT NULL
)
GO
INSERT INTO [#XMLTable]
SELECT 1,999,'Errors','xxx','xxxx','xx111',9999,888,'Address1' union all
SELECT 2,999,'Errors','xxx','xxxx','xx111',9999,888,'Address2' union all
SELECT 3,999,'Errors','xxx','xxxx','xx111',9999,888,'Address3' --union all
SELECT * FROM [#XMLTable]
/*
-- Expected output
<?xml version=”1” encoding=”utf-8”?>
<Batch>
BatchID=”1” Count=”999” Status=”Errors”
<Doc>
Type=”XXX” Code = “XXXX” Number=”xx111” Id=”9999”
<Error>888</Error>
<Address>Address1</Address>
</Doc>
</Batch>
*/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

Jampandu (8/2/2016)
/* -- Expected output <?xml version=”1” encoding=”utf-8”?> <Batch> BatchID=”1” Count=”999” Status=”Errors” <Doc> Type=”XXX” Code = “XXXX” Number=”xx111” Id=”9999” <Error>888</Error> <Address>Address1</Address> </Doc> </Batch> */

Are you sure you don't want your output to look more like this (that is with the attributes inside the tags)?

<Batch BatchID="1" Count="999" Status="Errors">

<Doc Type="XXX" Code="XXXX" Number="xx111" Id="9999">

<Error>888</Error>

<Address>Address1</Address>

</Doc>

</Batch>

And do you really only want the first row?

Also, this seems fairly straightforward to me. What have you tried and where are you running into problems?

Drew

Edit: Added missing closing angle bracket.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jampandu Ten Centuries Points: 1062 More actions · Answer 2

Hi Allen,

I tried this using below query and I am not able add the elements inside other elements like doc

SELECT *

FROM [#XMLTable]

for xml path(''), root('Batch')

I need line one as well.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

Quick example using FOR XML PATH

😎

USE TEEST;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#XMLTable') IS NOT NULL DROP TABLE #XMLTable;

CREATE TABLE [#XMLTable](

[BatchID] [nvarchar](25) NOT NULL,

[Count] [int] NOT NULL,

[Status] [varchar](6) NOT NULL,

[Type] [varchar](10) NOT NULL,

[Code] [varchar](10) NOT NULL,

[Number] [varchar](100) NOT NULL,

[ID] [int] NOT NULL,

[Error] [int] NOT NULL,

[Address] [nvarchar](1023) NOT NULL

)

GO

INSERT INTO [#XMLTable]

SELECT 1,999,'Errors','xxx','xxxx','xx111',9999,888,'Address1' union all

SELECT 2,999,'Errors','xxx','xxxx','xx111',9999,888,'Address2' union all

SELECT 3,999,'Errors','xxx','xxxx','xx111',9999,888,'Address3'

SELECT

XT.BatchID AS '@BatchID'

,XT.Count AS '@Count'

,XT.Status AS '@Status'

,XT.Type AS 'Doc/@Type'

,XT.Code AS 'Doc/@Code'

,XT.Number AS 'Doc/@Number'

,XT.ID AS 'Doc/@Id'

,XT.Error AS 'Doc/Error'

FROM [#XMLTable] XT

FOR XML PATH('Batch'), ROOT('Batches'),TYPE;

The output

<Batches>

<Batch BatchID="1" Count="999" Status="Errors">

<Doc Type="xxx" Code="xxxx" Number="xx111" Id="9999">

<Error>888</Error>

</Doc>

</Batch>

<Batch BatchID="2" Count="999" Status="Errors">

<Doc Type="xxx" Code="xxxx" Number="xx111" Id="9999">

<Error>888</Error>

</Doc>

</Batch>

<Batch BatchID="3" Count="999" Status="Errors">

<Doc Type="xxx" Code="xxxx" Number="xx111" Id="9999">

<Error>888</Error>

</Doc>

</Batch>

</Batches>