Hi everyone,
I have generated a XML file of a database in SQL which is printing some not wanted data.
How can I skip this data being generated with the file?
I just want a simple file with normal nametags and if null value is there then just print that tag....
Please see screenshot:
Can you provide DDL, sample data and the query you used to generate the script above. Also, if the value is null, do you want to not show the attribute?
What is thw purpose of this xml file? What uses it
Yes I want to show the attribute.
How do I provide you the data? The database is too big.
This is the query I used:
SELECT TOP 100 (
SELECT TOP 3
--[List_ID]
-- ,
[List_Type]
--,[List_Description]
-- ,[List_Provider]
-- ,[Issuing_Body]
,
(
SELECT TOP 100
--[UniqueID]
--,
[Record_ID]
,[Record_Type]
,[Title]
,[Alternate_Title]
,[Full_Name]
,[Gender]
,[Nationality]
,[Source]
,[Date_of_Capture]
,[Soft_Delete]
,[Date_of_Soft_Delete]
--,[Catefor xml path ry]
--,[Sub_Catefor xml path ry]
,[Listed_On]
,[Modified_On]
--,[Individual_List_ID_FK]
,
(
SELECT TOP 100
--[NatId_PK]
-- ,[NatId_Record_ID_FK]
--,[NatId_UniqueID_FK]
--,
[NatID_Type]
,[NatID_Num]
,[NatID_issue]
,[NatID_expiry]
,[NatID_State_Province]
,[NatID_Country]
,[isNationality]
from National_Identity
--where NatId_Record_ID_FK=Record_ID
where NatId_UniqueID_FK=UniqueID
for xml path('National_Identity'), root('National_Identitities') , elements xsinil, type) as [*],
(
SELECT TOP 100
--[Add_Id]
--,
[Address_Line_1]
,[Address_Line_2]
,[Address_Line_3]
,[Address_Line_4]
,[Town_District]
,[City]
,[County_State]
,[Post_Zip_Code]
,[Country]
,[ISO_Country]
,[Soft_Delete_Address]
--,[Address_Record_ID_FK]
--,[Address_UniqueID_FK]
From Address
where Address_UniqueID_FK = UniqueID
--where Address_Record_ID_FK = Record_ID
for xml path('Address'), root('Addresses'), elements xsinil,type) as [*],
(
SELECT TOP 100
--[AliasID]
--,
[Title]
,[Alternate_Title]
,[Forename]
,[Middle_Name]
,[Surname]
,[Full_Alias]
,[Soft_Delete]
-- ,[Alias_Record_ID_FK]
-- ,[Alias_UniqueID_FK]
from Alias
where Alias_UniqueID_FK = UniqueID
--where Alias_Record_ID_FK = Record_ID
for xml path('Alias'), root('Aliases'), elements xsinil, type) as [*],
(
SELECT TOP 100
--[Birth_ID]
--,[Birth_Details_Record_ID_FK]
--,[Birth_Details_UniqueID_FK]
-- ,
[Date_of_Birth]
,[Place_of_Birth]
,[Year_of_Birth]
from Birth_Details
--where Birth_Details_Record_ID_FK = Record_ID
where Birth_Details_UniqueID_FK = UniqueID
for xml path('Birth_Detail'), root('Birth_Details'), elements xsinil, type) as [*],
(
SELECT TOP 100
--[Death_ID]
-- ,[Death_Details_Record_ID_FK]
-- ,[Death_Details_UniqueID_FK]
-- ,
[Date_of_Death]
,[Year_of_Death]
,[Place_of_Death]
from Death_Details
--where Death_Details_Record_ID_FK = Record_ID
where Death_Details_UniqueID_FK = UniqueID
for xml path('Death_Detail'), root('Death_Details'), elements xsinil, type) as [*],
(
SELECT TOP 100
--[ConInfo_ID]
--,[ConInfo_Record_ID_FK]
--,[ConInfo_UniqueID_FK]
--,
[Home_Telephone]
,[Business_Telephone]
,[Mobile_Telephone]
,[Fax]
,[Email]
from Contact_Info
--where ConInfo_Record_ID_FK = Record_ID
where ConInfo_UniqueID_FK = UniqueID
for xml path('Contact_Info'), root('Contact_Infos'), elements xsinil, type) as [*],
(
SELECT TOP 100
[Individual_ID_2]
,[Description_of_Relationship]
,[Soft_Delete]
--,[IndAssoc_Record_ID_FK]
--,[IndAssoc_UniqueID_FK]
from Individual_Association
--where IndAssoc_Record_ID_FK = Record_ID
where IndAssoc_UniqueID_FK = UniqueID
for xml path('Individual_Association'), root('Individual_Associations'), elements xsinil, type) as [*],
(
SELECT TOP 100
--[Business_ID]
-- ,
[Business_Name]
,[Short_Name]
,[Registered_At]
,[Registration_Number]
,[Date_of_Incorportion]
,[Country]
,[City]
,[State]
,[Province]
,[Description]
,[Soft_Delete]
-- ,[BusAssoc_Record_ID_FK]
-- ,[BusAssoc_UniqueID_FK]
from Business_Association
--where BusAssoc_Record_ID_FK= Record_ID
where BusAssoc_UniqueID_FK= UniqueID
for xml path('Business_Association'), root('Business_Associations'), elements xsinil, type) as [*],
(
SELECT TOP 100
--[Art_Id]
-- ,
[Original_URL]
,[Provider_URL]
,[File_Name]
,[Source]
,[Date_of_Capture]
--,[Article_Record_ID_FK]
--,[Article_UniqueID_FK]
,[Title]
,[SnippetText]
,[Adverse_Terms]
from Article
--where Article_Record_ID_FK= Record_ID
where Article_UniqueID_FK= UniqueID
for xml path('Article'), root('Articles'), elements xsinil, type) as [*],
(
SELECT TOP 100
--[Note_Id]
--,
[Source_of_Notes]
,[Notes]
,[Soft_Delete]
-- ,[Note_Record_ID_FK]
-- ,[Note_UniqueID_FK]
from Note
--where Note_Record_ID_FK= Record_ID
where Note_UniqueID_FK= UniqueID
for xml path('Note'), root('Notes'), elements xsinil, type) as [*],
(
SELECT TOP 100
--[PolPos_ID]
-- ,
[Description]
,[From]
,[To]
,[Country]
,[Soft_Delete]
--,[PolPos_Record_ID_FK]
--,[PolPos_UniqueID_FK]
,[PEP_Tier]
,[Designation]
,[Institution]
,[Risk_Category]
from Political_Position
--where PolPos_Record_ID_FK= Record_ID
where PolPos_UniqueID_FK= UniqueID
for xml path('Political_Position'), root('Political_Positions'), elements xsinil, type) as [*],
(
SELECT TOP 100
--[PubDet_Record_ID_FK]
--[PubDet_UniqueID_FK]
--,
[PubDet_ID]
,[FullAccessURL]
,[LimitedAccessURL]
from Publishing_Details
--where PubDet_Record_ID_FK= Record_ID
where PubDet_UniqueID_FK= UniqueID
for xml path('Publishing_Detail'), root('Publishing_Details'), elements xsinil, type) as [*],
(
SELECT TOP 100
--[PIden_Record_ID_FK]
--[PIden_UniqueID_FK]
-- ,[PIden_ID]
--,
[Photo]
,[File_Name1]
,[URL_Photo]
,[Date_of_Capture]
from Photo_Identity
--where PIden_Record_ID_FK= Record_ID
where PIden_UniqueID_FK= UniqueID
for xml path('Photo_Identity'), root('Photo_Identities'), elements xsinil, type) as [*]
from Individual
WHERE [Individual_List_ID_FK] = [List_ID]
for xml path ('Individual'), root('Individuals'), elements xsinil, type) as [*]
from [dbo].[List]
--where List_ID = [Individual_List_ID_FK]
for xml path('List'), root ('Lists'), elements xsinil, type) as [final]
Why does it matter?
As @GHW901 said, it does not matter. What you are showing is perfectly valid XML. When you construct an XML from values in a table, and if a value is null, you can either choose to omit that element, or show it as null using xsi:nil construct as you have in your example.
If I understand your wording, if a value is null, what you want is to show is, something like "<Address_Line_1/>" or "<Address_Line_1><Address_Line_1>". But this does not represent a null. It represents an empty string.
You can see this from the following example:
DROP TABLE IF EXISTS #tmp;
CREATE TABLE #tmp(Col1 VARCHAR(32), Col2 VARCHAR(32));
INSERT INTO #tmp VALUES ('x1','y1'),('x2',NULL),('x3','');
SELECT * FROM #tmp FOR XML PATH('tmp'),ROOT('Document'), ELEMENTS XSINIL;
This produces the following output.
<Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<tmp>
<Col1>x1</Col1>
<Col2>y1</Col2>
</tmp>
<tmp>
<Col1>x2</Col1>
<Col2 xsi:nil="true" />
</tmp>
<tmp>
<Col1>x3</Col1>
<Col2></Col2>
</tmp>
</Document>
Notice the difference col2 is represented when the value is null vs when it is an empty string.
If you don't want the null elements at all (i.e., the xsi:nil="true"), then omit the "ELEMENTS XSINIL" from the query.
If you want to represent null values as empty strings, use COALESCE. That is, in this example, instead of "SELECT *", you would do "SELECT COALESCE(Col1,''),COALESCE(Col2,'')".
Ok I will check this and confirm whether its working or not.
Also what about the information coming in main tags?
like for example:
<Individual>
<Record_ID>I9500039</Record_ID>
<Record_Type xsi:nil="true" />
<Title xsi:nil="true" />
<Alternate_Title xsi:nil="true" />
<National_Identitities xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<National_Identity>
<NatID_Type>Italian</NatID_Type>
</National_Identity>
</National_Identitities>
<Addresses xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Address>
<Address_Line_1 xsi:nil="true" />
<Address_Line_2 `Preformatted text`xsi:nil="true" />
<Address_Line_3 xsi:nil="true" />
</Address>
</Addresses>
I dont want this information to print: <National_Identitities xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
but just simply <National_Identities>
and other place this link is printing...
it matters because many of us have worked with xml for many years. so by you sharing what it is used for we might be able to provide you guidance with maybe even avoiding the use of xml as it can get pretty complicated and brittle. If you are interested that is. or we can just provide you on how to generate the xml
you need to provide DDL and DML for all of these tables
National_Identity
Address
Alias
Birth_Details
Death_Details
Contact_Info
Individual_Association
Business_Association
Article
Note
Political_Position
Publishing_Details
Photo_Identity
Individual
meaning in line sql script
create table #National_Identity(NatID_Type varchar(50),NatID_Num int, ) --etc
insert into #National_Identity
select 'VET', 3 --etc
This helps us emulate your data (which we do not have access to) in our environment.
Ah I see. Thank you for explaining it so well. Basically I have been given this task from someone and they asked to complete it like this. So I have to convert my SQL Tables data into XML so it can be used somewhere else.
Can you please tell me whats the best way to copy all the queries and send it here? I want to learn the procedure.
I checked using COALESCE but its not working. Its actually printing the data horizontally now and incomplete.
Give the columns names as in
SELECT
COALESCE(Col1,'') as Col1,
COALESCE(Col2,'') as Col2
The reference to "http://www.w3.org/2001/XMLSchema-instance" tells the XML parser that will be reading your xml document which schema instance namespace is to be used for interpreting the special attributes (in this case xsi:nil). If you omit it most parsers will still parse the document correctly, but to be precise you need to include it.
Already did here ^^
DROP TABLE IF EXISTS #National_Identity;
CREATE TABLE #National_Identity(
NatId_UniqueID_FK int,
[NatID_Type] varchar(50)
,[NatID_Num] int
,[NatID_issue] varchar(50)
,[NatID_expiry] datetime
,[NatID_State_Province] CHAR(2)
,[NatID_Country] varchar(50)
,[isNationality] bit
);
INSERT INTO #National_Identity(NatId_UniqueID_FK,[NatID_Type]
,[NatID_Num]
,[NatID_issue]
,[NatID_expiry]
,[NatID_State_Province]
,[NatID_Country]
,[isNationality])
VALUES (1, 'XDD',1,'None',getdate(),'ON','Canada',1);
DROP TABLE IF EXISTS #Address;
CREATE TABLE #Address(
Address_UniqueID_FK int,
[Address_Line_1] varchar(50)
,[Address_Line_2] varchar(50)
,[Address_Line_3] varchar(50)
,[Address_Line_4] varchar(50)
,[Town_District] varchar(50)
,[City] varchar(50)
,[County_State] varchar(50)
,[Post_Zip_Code] varchar(50)
,[Country] varchar(50)
,[ISO_Country] varchar(50)
,[Soft_Delete_Address] bit
);
INSERT INTO #Address(Address_UniqueID_FK,
[Address_Line_1]
,[Address_Line_2]
,[Address_Line_3]
,[Address_Line_4]
,[Town_District]
,[City]
,[County_State]
,[Post_Zip_Code]
,[Country]
,[ISO_Country]
,[Soft_Delete_Address])
VALUES (1, '12345 Kiev Street',null, null,null,'Donbas','Kyiv','Donbas','1234-99','Ukraine','UA',1),
(1, null, '111 Dobvrisky Street',null,null,'LVIV','Khershon','Donbas','1234-99','Ukraine','UA',1);
SELECT a.*
FROM #National_Identity ni
join #Address a on ni.NatId_UniqueID_FK = a.Address_UniqueID_FK
FOR XML PATH('addresses'),ROOT('Document')
--, ELEMENTS XSINIL;
Thank you. It works. I was missing "as" keyword.
I understand about the link but my requirement is to remove it. Even if I face the problem later I will change it back but it would be really helpful if I remove it right now.
Awesome.
But this is not printing the tags will null values.
My requirement is that if there is a tag will NULL then it should just simply print that tag like this:
<Title />
<Alternate_Title />
This one is resolved by @JamesK
Only thing is remaining is the link:
<Individuals xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
I just want to hide it from the final output.
so my XML file would look like this:
yes I am also searching for the same answer like the equation for null
